SQL触发器

SQL第16章SQL触发器前面已经介绍过了表、视图、存储过程以及函数的创建。一般而言，创建这些对象后，需要配置一些对应的操作。例如，执行SELECT语句查询数据，执行EXEC命令执行存储过程等。SQL也支持自动执行的对象，对数据的更改作出反应，即触发器。16.1触发器的基本概念触发器是一种特殊的存储过程，它在表的数据变化时发生作用。触发器可以维护数据完整性。16.1.1触发器简介触发器在数据库里以独立的对象存储，与存储过程不同的是，存储过程通过其他程序来启动运行，而触发器是由一个事件来启动运行。即当某个事件发生时，触发器自动地隐式运行。并且，触发器不能接收参数。触发器对象定义了触发器的特征和被调用时采取的行动。而这些动作是通过一个或多个SQL语句来实现的。SQL支持3种类型的触发器：INSERT（插入）、UPDATE（更新）和DELETE（删除）。当向表中插入数据、更新数据或删除数据时，触发器就被调用。通过给表定义一个或多个触发器，可以指定哪个数据修改时，可以激发触发器。16.1.2触发器执行环境触发器执行环境包含了触发器正确执行所必需的信息。这些信息主要是触发器本身的细节和触发器所定义的目标表。另外，触发器执行环境还包括一个或两个测试表，称之为INSERTED表和DELETED表。测试表是虚表，用于保存目标表更新、插入或删除的数据信息。这些测试表用来测试数据修改的结果，以及设置触发器行动的条件。用户不能直接修改测试表中的数据，但能在SELECT语句中，使用这些表来检测INSERT，UPDATE或DELETE的结果。各种类型触发器用到的测试表如图16.1所示（针对SQLServer数据库系统而言）。326第16章SQL触发器SQL**…新数据INSERTtrigger表Insered表**…被删除数据DELETEtrigger表Deleted表**…被删除数据UPDATEtrigger表Deleted表新数据（修改的数据）Insered表修改的数据图16.1触发器测试表deleted表存放了DELETE和UPDATE语句中相关行的副本。在DELETE或UPDATE语句的执行中，这些相关行从trigger表中移到了deleted表中。一般情况下，这两张表中无共同行。insertded表存放了INSERT和UPDATE语句中的副本。在INSERT或UPDATE语句的执行中，这些新行同时被加到inserted表和trigger表中。inserted表中的行是trigger表中新行的副本。一个UPDATE效果上等价于一个DELETE再接着一个INSERT。首先“旧”行被复制到deleted表中，然后新行被复制到trigger表和inserted表中。16.2SQLServer中的触发器当对某一表进行操作时，SQLServer就会自动执行触发器所定义的SQL语句，从而确保对数据的处理必须符合规则。触发器的作用就是保证参照完整性和数据的一致性。16.2.1SQLServer触发器的种类SQLServer2000及其以后的版本支持两种类型的触发器：AFTER触发器和INSTEADOF触发器。其中AFTER触发器即为前面介绍的UPDATE、INSERT、DELETE触发器。INSTEADOF触发器表示并不执行其所定义的操作（INSERT、UPDATE、DELETE），而仅是执行触发器本身。既可在表上定义INSTEADOF触发器，也可以在视图上定INSTEADOF触发器，但对同一操作只能定义一个INSTEADOF触发器。16.2.2使用CREATETRIGGER命令创建触发器在SQLServer中，可以采用CREATETRIGGER命令创建触发器。语法如下。CREATETRIGGERtrigger_nameON{table|view}[WITHENCRYPTION]{{{FOR|AFTER|INSTEADOF}{[DELETE][,][INSERT][,][UPDATE]}32716.2SQLServer中的触发器SQL[NOTFORREPLICATION]AS[{IFUPDATE(column)[{AND|OR}UPDATE(column)][...n]|IF(COLUMNS_UPDATED(){bitwise_operator}updated_bitmask){comparison_operator}column_bitmask[...n]}]sql_statement[...n]}}说明如下。trigger_name：为用户要创建的触发器的名字，触发器的名字必须符合SQLServer的命名规则，且其名字在当前数据库中必须是惟一的。Table、view：与触发器相关联的表或视图的名字，并且该表或视图必须已经在数据库中存在。WITHENCRYPTION：表示对含有CREATETRIGGER文本的syscomments表进行加密，防止用户通过查询syscomments表获取触发器的代码。AFTER：表示只有执行了指定的操作（INSERT、DELETE、或UPDATE）之后，触发器才被激活，执行触发器中的SQL语句。FOR：表示为AFTER触发器，且该触发器仅能在表上创建。INSTEADOF：指定触发器为INSTEADOF触发器。说明每个表最多只能有一个INSTEADOF（INSERT、UPDATE、DELETE）触发器。然而可以为每个表创建多个视图，对每个视图都可以有不同的INSTEADOF触发器。有关INSTEADOF触发器的详细信息在16.2.6节会有详细介绍。DELETE、INSERT、UPDATE：指明执行哪种操作，将激活触发器。至少要包含3种操作类型种的一种，也可以是3种操作语句的任意组合。其中三者的顺序不受限制，且各选项要用逗号隔开。NOTFORREPLICATION：告诉DBMS，当复制表时，触发器不能被执行。AS：后面列出触发器将要执行的动作。IFUPDATEcolumn：用来测定对某一确定列是INSERT操作还是UPDATE操作。如果要测试INSERT还是UPDATE操作的列多于一列，可用AND或OR逻辑连接向IFUPDATE子句添加所希望的附加列名。IFCOLUMNS_UPDATED()：仅在INSERT和UPDATE类型的触发器中使用，检查列是被更新还是被插入。bitwise_operator：代表位逻辑运算符，常用“&”。updated_bitmask：表示列的整位掩码。其中最右边的位表示表或视图的第1列，左边第2位代表第2列，依此类推。comparison_operator：表示比较操作符。可以是“=”或者“”。“=”表示检查在updated_bitmask中定义的所有列是否都被更新，用“”表示检查是否在updated_bitmask328第16章SQL触发器SQL中定义的某些列被更新。column_bitmask：在IFCOLUMNS_UPDATED()子句中，要测试的是否被更新的列的序号的掩码。说明为了便于理解，这里给出一个使用IFCOLUMNS_UPDATED()子句的例子。如果表T包括C1、C2、C3、C4、C5和C66列，为了检查C2、C4或者C6列是否更新过，可使用42（二进制表示为“101010”）作为掩码，表示为：IF（COLUMNS_UPDATED()&42）0；如果检查C2、C4和C63列是否都被更新过，表示为：IF（COLUMNS_UPDATED()&42）=42。sql_statement：代表包含在触发器中的处理语句。当不再需要触发器时，可用DROPTRIGGER语句删除触发器。语法如下。DROPTRIGGERtrigger_name[...n]16.2.3INSERT触发器实例1创建INSERT触发器为STUDENT表创建触发器S_insert，当向STUDENT表中插入数据时，要求学号必须以“97”开头，且课程号CNO必须在COURSE表中，否则取消插入操作。实例代码如下。CREATETRIGGERS_insertONSTUDENTFORINSERTASDECLARE@S_noVARCHAR(4),@S_cnoINTSELECT@S_no=SNO,@S_cno=CNOFROMINSERTEDIF(LEFT(@S_no,2)!='97')BEGINROLLBACKTRANSACTIONRAISERROR('输入的学号：%s不是97级的学生，请确认后重新录入！',16,1,@S_no)ENDIF(@S_cnoNOTIN(SELECTCNOFROMCOURSE))BEGINROLLBACKTRANSACTIONRAISERROR('输入的课程号：%d在COURSE表中不存在，请确认后重新录入！',16,1,@S_cno)END当通过如下语句向STUDENT表中插入数据时：INSERTINTOSTUDENTVALUES('9602','王永','机械工程','男',2,76,'必修')由于插入数据的学号为“9602”，并不是以“97”开头，所以执行S_insert触发器时，将执行“ROLLBACKTRANSACTION”语句，取消完成的工作，并执行RAISERROR语句给出错误信息。运行结果如下。32916.2SQLServer中的触发器SQL输入的学号：9602不是97级的学生，请确认后重新录入！当通过如下语句向STUDENT表中插入数据时：INSERTINTOSTUDENTVALUES('9702','王永','机械工程','男',12,76,'必修')由于课程号12在COURSE表中不存在，所以执行S_insert触发器时，将执行“ROLLBACKTRANSACTION”语句，取消工作，并执行RAISERROR语句给出错误信息。运行结果：输入的课程号：12在COURSE表中不存在，请确认后重新录入！可以在一个表上创建多个触发器，数据库把一个表中所有触发器都看作同一事务的一部分。因此只要其中一个触发器执行了ROLLBACKTRANSACTION语句，那么所有的操作（与该INSERT语句有关）都将被取消。16.2.4DELETE触发器实例2创建DELETE触发器为STUDENT表创建DELETE触发器S_delete，当一次删除的记录大于一行或者删除记录的课程为本系教师所开设时，取消删除操作。实例代码：CREATETRIGGERS_deleteONSTUDENTFORDELETEASDECLARE@rowcountintSELECT@rowcount=@@ROWCOUNTIF@rowcount1BEGINROLLBACKTRANSACTIONRAISERROR('当前要删除的记录数为%d,一次只允许删除一行记录！',16,1,@rowcount)ENDDECLARE@S_dnameVARCHAR(16),@S_cnoINTSELECT@S_dname=DNAME,@S_cno=CNOFROMDELETEDIF(@S_cnoIN(SELECTCNOFROMTEACHERWHEREDNAME=@S_dname))BEGINROLLBACKTRANSACTIONRAISERROR('删除记录的课程为本系即%s系教师所开设，不允许删除！',16,1,@S_dname)END当通过如下语句从STUDENT表中删除数据时：DELETESTUDENTWHERESNO=’9706’运行结果如下。当前要删除的记录数为4,一次只允许删除一行记录！330第16章SQL触发器SQL当用户从STUDENT表执行DELETE语句时，DBMS就激活触发器S_delete，该触发器首先判断要删除记录的行数，只要多于一条，就取消删除操作。当删除的记录只有一行时，触发器继续判断删除的记录的课程是否为本系教师所开设，如果是，就取消删除操作，否则就完成删除操作。如下面的删除代码：DELETESTUDENTWHERESNO=’9705’ANDCNO=8运行结果如下。删除记录的课程为本系即电子工程系教师所开设，不