第11章 触发器

1、11.1.1 11.1.1 触发器的作用触发器的作用l 触发器是被自动执行的，不需要显式调用。触发器是被自动执行的，不需要显式调用。l 触发器可以调用存储过程。触发器可以调用存储过程。l 触发器可以强化数据条件约束。触发器可以强化数据条件约束。l 触发器可以禁止或回滚违反引用完整性的数据触发器可以禁止或回滚违反引用完整性的数据修改或删除。修改或删除。l 利用触发器可以进行数据处理。利用触发器可以进行数据处理。l 触发器可以级联、并行执行。触发器可以级联、并行执行。l 在同一个表中可以设计多个触发器。在同一个表中可以设计多个触发器。11.1.2 11.1.2 触发器的种类触发器的种类l DMLD

2、ML触发器：触发器：在执行数据操作语言（在执行数据操作语言（DMLDML）事件时被调用）事件时被调用的触发器，包括的触发器，包括INSERTINSERT、UPDATEUPDATE和和DELETEDELETE语句。语句。l DDLDDL触发器：触发器：在执行数据定义语言（在执行数据定义语言（DDLDDL）事件时被调用）事件时被调用的触发器，包括的触发器，包括CREATECREATE、ALTERALTER和和DROPDROP语句。语句。l 登录触发器：登录触发器：为响应登录事件而触发的存储过程。为响应登录事件而触发的存储过程。 基本的触发器事件基本的触发器事件种种 类类关关 键键 字字含含 义义D

3、MLDML事件事件INSERTINSERT在表或视图中插入数据时触发在表或视图中插入数据时触发UPDATEUPDATE修改表或视图中的数据时触发修改表或视图中的数据时触发DELETEDELETE在删除表或视图中的数据时触发在删除表或视图中的数据时触发DDLDDL事件事件CREATECREATE在创建新对象时触发在创建新对象时触发ALTERALTER修改数据库或数据库对象时触发修改数据库或数据库对象时触发DROPDROP删除对象时触发删除对象时触发登录事件登录事件LOGONLOGON当用户连接到数据库并建立会话时触发当用户连接到数据库并建立会话时触发11.2.1 11.2.1 DMLDML触发器

4、概述触发器概述 DMLDML触发器是定义在表上的触发器，由触发器是定义在表上的触发器，由DMLDML事件引发。可事件引发。可以用来防止恶意或错误的数据库表更新操作。创建以用来防止恶意或错误的数据库表更新操作。创建DMLDML触发触发器的要素如下：器的要素如下： 确定触发基于的表确定触发基于的表( (基表基表) )，即在其上定义触发器的表。，即在其上定义触发器的表。 确定触发的事件，确定触发的事件，DMLDML触发器的触发事件有触发器的触发事件有INSERTINSERT、UPDATEUPDATE和和DELETEDELETE三种，但不包括三种，但不包括SELECTSELECT语句。语句。 确定触发

5、时间，有触发动作发生在确定触发时间，有触发动作发生在DMLDML语句执行之前和语语句执行之前和语句执行之后两个情况，所以，句执行之后两个情况，所以，DMLDML触发器又分为以下两个触发器又分为以下两个类型：类型：l AFTERAFTER触发器：在执行触发事件之后执行触发器：在执行触发事件之后执行AFTERAFTER触发器。触发器。如果违反了约束，则永远不会执行如果违反了约束，则永远不会执行AFTERAFTER触发器；因此，触发器；因此，这些触发器不能用于任何可能防止违反约束的处理。这些触发器不能用于任何可能防止违反约束的处理。l INSTEAD OFINSTEAD OF触发器：触发器：INST

6、EAD OFINSTEAD OF触发器替代触发器语触发器替代触发器语句的标准操作。因此，触发器可用于对一个或多个列句的标准操作。因此，触发器可用于对一个或多个列执行错误或值检查，然后在插入、更新或删除行之前执行错误或值检查，然后在插入、更新或删除行之前执行其他操作。执行其他操作。 执行触发器操作的语句。执行触发器操作的语句。11.2.2 11.2.2 创建创建DMLDML触发器触发器1. 1. 使用使用SQL ServerSQL Server管理控制器创建管理控制器创建DMLDML触发器触发器 【例例11.111.1】 使用使用SQL ServerSQL Server管理控制器在管理控制器在s

7、tudentstudent表表上创建一个触发器上创建一个触发器trigoptrigop，其功能是在用户插入、修改或，其功能是在用户插入、修改或删除该表中行中输出所有的行。删除该表中行中输出所有的行。 启动启动SQL ServerSQL Server管理控制器，在管理控制器，在“对象资源管理器对象资源管理器”中展开中展开“LCBLCB-PC-PC”服务器节点。服务器节点。操作步骤操作步骤 展开展开“数据库数据库”| |“schoolschool”| |“表表”| |“studentstudent”| |“触触发器发器”节点，单击鼠标右键，在出现的快捷菜单中选择节点，单击鼠标右键，在出现的快捷菜单

8、中选择“新建触发器新建触发器”命令。命令。 出现一个新建触发器编辑窗口，其中包含触发器模板，用出现一个新建触发器编辑窗口，其中包含触发器模板，用户可以参照模板在其中输入触发器的户可以参照模板在其中输入触发器的T-SQLT-SQL语句，这里输入语句，这里输入的语句如下（其中的语句如下（其中红字红字部分为主要输入的部分为主要输入的T-SQLT-SQL语句）：语句）：SET SET ANSI_NULLSANSI_NULLS ON ONGOGOSET SET QUOTED_IDENTIFIERQUOTED_IDENTIFIER ON ONGOGOCREATE TRIGGER CREATE TRIGGE

9、R trigoptrigopON ON studentstudent AFTER AFTER INSERT,DELETE,UPDATEINSERT,DELETE,UPDATEAS AS BEGINBEGINSET SET NOCOUNTNOCOUNT ON ONSELECT SELECT * * FROM student FROM studentENDENDGOGO 单击工具栏中的单击工具栏中的 按钮，将该触发器保存到相关的系统按钮，将该触发器保存到相关的系统表中。这样就创建了触发器表中。这样就创建了触发器trigoptrigop。 在触发器在触发器trigoptrigop创建完毕，当对创建完

10、毕，当对studentstudent表进行记录插入、表进行记录插入、修改或删除操作时，触发器修改或删除操作时，触发器trigoptrigop都会被自动执行。例如，执都会被自动执行。例如，执行以下程序：行以下程序：USE schoolUSE schoolGOGOINSERT student VALUES(1,INSERT student VALUES(1,刘明刘明,男男,1992-12-,1992-12-12,1005)12,1005)GOGO 当向当向studentstudent表中插入一个记录时自动执行触发器表中插入一个记录时自动执行触发器trigoptrigop输出其所有记录，输出结果如图

11、输出其所有记录，输出结果如图11.111.1所示，从中看到新记录所示，从中看到新记录已经插入到已经插入到studentstudent表中了。表中了。2. 2. 使用使用T-SQLT-SQL语句创建语句创建DMLDML触发器触发器 创建创建DMLDML触发器可以使用触发器可以使用CREATE TRIGGERCREATE TRIGGER语句，其基本语句，其基本语法格式如下：语法格式如下：CREATE TRIGGER CREATE TRIGGER 触发器名触发器名 ON ON 表名表名 | | 视图名视图名 WITH ENCRYPTION WITH ENCRYPTION FOR | AFTER |

12、INSTEAD OF INSERT , UPDATE, DELETE FOR | AFTER | INSTEAD OF INSERT , UPDATE, DELETE NOT FOR REPLICATION NOT FOR REPLICATION AS AS IF UPDATE ( IF UPDATE (列名列名) ) AND | OR UPDATE ( AND | OR UPDATE (列名列名) ) n n SQL SQL语句语句 n n 说明：说明：UPDATE (UPDATE (列名列名) )返回一个布尔值，指示是否尝试对表或返回一个布尔值，指示是否尝试对表或视图的指定列执行视图的指定

13、列执行INSERTINSERT或或UPDATEUPDATE操作。可以在操作。可以在INSERTINSERT或或UPDATEUPDATE触发器中的任意位置使用触发器中的任意位置使用UPDATE()UPDATE()，以测试触发器是，以测试触发器是否应执行某些操作。否应执行某些操作。 【例例11.211.2】在数据库在数据库testtest中建立一个表中建立一个表table20table20，创建一个，创建一个触发器触发器trigtesttrigtest，在，在table20table20表中插入、修改和删除记录时，自表中插入、修改和删除记录时，自动显示表中的所有记录。并用相关数据进行测试。动显示表

14、中的所有记录。并用相关数据进行测试。USE testUSE testGOGOCREATE TABLE CREATE TABLE table20table20- -创建表创建表table20table20( (c1c1 intint, ,c2c2 char(30) char(30) )GOGOCREATE TRIGGER CREATE TRIGGER trigtesttrigtest- -创建触发器创建触发器trigtesttrigtestON ON table20table20 AFTER AFTER INSERT,UPDATE,DELETEINSERT,UPDATE,DELETE ASASS

15、ELECT SELECT * * FROM FROM table20table20GOGO在执行下面的语句时：在执行下面的语句时：USE testUSE testGOGOINSERT INSERT Table20Table20 VALUES( VALUES(1,Name11,Name1)GOGO结果会显示出结果会显示出table20table20表中的行如图表中的行如图11.211.2所示。所示。在执行下面的语句时：在执行下面的语句时：USE testUSE testGOGOUPDATE UPDATE Table20Table20 SET SET c2c2=Name2Name2 WHERE W

16、HERE c1c1=1=1GOGO 结果会显示出结果会显示出table20table20表中的记录行如图表中的记录行如图11.311.3所示。所示。 从中看到，只有在包含触发事件的语句成功执行后，才从中看到，只有在包含触发事件的语句成功执行后，才会执行相应的触发器。会执行相应的触发器。3. 3. 创建创建DMLDML触发器的注意事项触发器的注意事项l CREATE TRIGGERCREATE TRIGGER语句必须是批处理中的第一个语句。将语句必须是批处理中的第一个语句。将该批处理中随后的其他所有语句解释为该批处理中随后的其他所有语句解释为CREATE TRIGGERCREATE TRIGGE

17、R语句定义的一部分。并且只能应用于一个表。语句定义的一部分。并且只能应用于一个表。l 触发器只能在当前的数据库中创建，但是可以引用当前触发器只能在当前的数据库中创建，但是可以引用当前数据库的外部对象。数据库的外部对象。l 虽然触发器可以引用当前数据库以外的对象，但只能在虽然触发器可以引用当前数据库以外的对象，但只能在当前数据库中创建触发器。当前数据库中创建触发器。l 触发器体内禁止使用触发器体内禁止使用COMMITCOMMIT、ROLLBACKROLLBACK、SAVEPOINTSAVEPOINT语语句，也禁止直接或间接地调用含有上述语句的存储过程。句，也禁止直接或间接地调用含有上述语句的存储

18、过程。11.2.3 11.2.3 触发器的删除、禁用和启用触发器的删除、禁用和启用1. 1. 删除删除DMLDML触发器触发器删除触发器使用删除触发器使用DROP TRIGGERDROP TRIGGER命令，其基本语法格式如下：命令，其基本语法格式如下：DROP TRIGGER DROP TRIGGER 触发器名触发器名 , ,n n 【例例11.311.3】给出删除给出删除schoolschool数据库中数据库中studentstudent表上表上trigoptrigop触发器的程序。触发器的程序。USE schoolUSE schoolGOGODROP TRIGGER DROP TRIGG

19、ER trigoptrigopGOGO2. 2. 禁用禁用DMLDML触发器触发器 禁用触发器可以使用禁用触发器可以使用DISABLE TRIGGERDISABLE TRIGGER命令，其基本命令，其基本语法格式如下：语法格式如下： DISABLE TRIGGER DISABLE TRIGGER 触发器名触发器名 ON ON 表名表名 【例例11.411.4】给出禁用给出禁用testtest数据库中数据库中table20table20表上表上trigtesttrigtest触发器的程序。触发器的程序。USE testUSE testGOGODISABLE TRIGGER DISABLE TRI

20、GGER trigtesttrigtest ON ON table20table20GOGO3. 3. 启用启用DMLDML触发器触发器 启用触发器可以使用启用触发器可以使用ENABLE TRIGGERENABLE TRIGGER命令，其基本命令，其基本语法格式如下：语法格式如下： ENABLE TRIGGER ENABLE TRIGGER 触发器名触发器名 ON ON 表名表名 【例例11.511.5】给出启用给出启用testtest数据库中数据库中table20table20表上表上trigtesttrigtest触发器的程序。触发器的程序。USE testUSE testGOGOENAB

21、LE TRIGGER ENABLE TRIGGER trigtesttrigtest ON ON table20table20GOGO11.2.4 inserted11.2.4 inserted表和表和deleteddeleted表表l deleteddeleted表用于存储表用于存储DELETEDELETE和和UPDATEUPDATE语句所影响的行的副语句所影响的行的副本。在执行本。在执行DELETEDELETE或或UPDATEUPDATE语句时，行从触发器表中删语句时，行从触发器表中删除，并传输到除，并传输到deleteddeleted表中。表中。deleteddeleted表和触发器表通

22、常表和触发器表通常没有相同的行。没有相同的行。l insertedinserted表用于存储表用于存储INSERTINSERT和和UPDATEUPDATE语句所影响的行的语句所影响的行的副本。在一个插入或更新事务处理中，新建行被同时添副本。在一个插入或更新事务处理中，新建行被同时添加到加到insertedinserted表和触发器表中。表和触发器表中。insertedinserted表中的行是触表中的行是触发器表中新行的副本。发器表中新行的副本。 在对具有触发器的表（简称为触发器表）进行操作时，其在对具有触发器的表（简称为触发器表）进行操作时，其操作过程如下：操作过程如下：l 执行执行INSE

23、RTINSERT操作，插入到触发器的表中的新行被插入到操作，插入到触发器的表中的新行被插入到insertedinserted表中。表中。l 执行执行DELETEDELETE操作，从触发器表中删除的行被插入到操作，从触发器表中删除的行被插入到deleteddeleted表中。表中。l 执行执行UPDATEUPDATE操作，先从触发器表中删除旧行，然后再插入新操作，先从触发器表中删除旧行，然后再插入新行。其中被删除的旧行被插入到行。其中被删除的旧行被插入到deleteddeleted表中，插入的新行表中，插入的新行被插入到被插入到insertedinserted表中。表中。 【例例11.611.6

24、】 编写一个程序说明编写一个程序说明insertedinserted表和表和deleteddeleted表的作用。表的作用。USE testUSE testGOGOIF IF OBJECT_IDOBJECT_ID( (trigtest,TRtrigtest,TR) IS NOT NULL) IS NOT NULL DROP TRIGGER DROP TRIGGER trigtesttrigtest- -若存在若存在trigtesttrigtest触发器，则删除之触发器，则删除之GOGODELETE DELETE table20table20- -删除删除table20table20表中记录表中

25、记录GOGOCREATE TRIGGER CREATE TRIGGER trigtesttrigtest- -创建触发器创建触发器trigtesttrigtest ON ON table20table20 AFTER AFTER INSERT,UPDATE,DELETEINSERT,UPDATE,DELETEASAS PRINT inserted PRINT inserted表表: : SELECT SELECT * * FROM inserted FROM inserted PRINT deleted PRINT deleted表表: : SELECT SELECT * * FROM del

26、eted FROM deletedGOGO如果此时执行下面的如果此时执行下面的INSERTINSERT语句：语句：USE testUSE testGOGOINSERT INSERT table20table20 VALUES( VALUES(2,Name32,Name3)GOGO其执行结果如图其执行结果如图11.411.4所示。所示。如果此时接着执行下面的如果此时接着执行下面的UPDATEUPDATE语句：语句：USE testUSE testGOGOUPDATE UPDATE table20table20 SET SET c2c2=Name4Name4 WHERE WHERE c1c1=2=

27、2GOGO其执行结果如图其执行结果如图11.511.5所示。所示。如果此时接着执行下面的如果此时接着执行下面的DELETEDELETE语句：语句：USE testUSE testGOGODELETE DELETE table20table20 WHERE WHERE c1c1=2=2GOGO其执行结果如如图其执行结果如如图11.611.6所示。所示。 该例结果看到，该例结果看到，table20table20是触发器表：是触发器表： 在插入记录时，插入的记录被插入到在插入记录时，插入的记录被插入到insertedinserted表中；表中； 在修改记录时，修改前的记录插入到在修改记录时，修改前的

28、记录插入到deleteddeleted表中，修表中，修改后的记录插入到改后的记录插入到insertedinserted表中；表中； 在删除记录时，删除后的记录被插入到在删除记录时，删除后的记录被插入到deleteddeleted表中。表中。11.2.5 INSERT11.2.5 INSERT、UPDATEUPDATE和和DELETEDELETE触发器的应用触发器的应用1. 1. 应用应用INSERTINSERT触发器触发器 当触发当触发INSERTINSERT触发器时，新的数据行就会被插入到触触发器时，新的数据行就会被插入到触发器表和发器表和insertedinserted表中。表中。 ins

29、erted inserted表中包含了已经插入的数据行（一行或多行）表中包含了已经插入的数据行（一行或多行）的一个副本。触发器通过检查的一个副本。触发器通过检查insertedinserted表来确定是否执行表来确定是否执行触发器动作或如何执行它。触发器动作或如何执行它。 【例例11.711.7】建立一个触发器建立一个触发器trignametrigname，当向，当向studentstudent表中插表中插入数据时，如果出现姓名重复的情况，则回滚该事务。入数据时，如果出现姓名重复的情况，则回滚该事务。USE schoolUSE schoolGOGOCREATE TRIGGER CREATE T

30、RIGGER trignametrigname-创建创建trignametrigname触发器触发器ON student AFTER INSERT ON student AFTER INSERT ASASBEGINBEGINDECLARE name char(10)DECLARE name char(10)SELECT name=inserted.SELECT name=inserted.姓名姓名 FROM inserted FROM insertedIF EXISTS(SELECT IF EXISTS(SELECT 姓名姓名 FROM student WHERE FROM student W

31、HERE 姓名姓名=name)=name)BEGINBEGINRAISERRORRAISERROR(姓名重复姓名重复, ,不能插入不能插入,16,1),16,1)ROLLBACKROLLBACK-事务回滚事务回滚ENDENDENDEND执行以下程序：执行以下程序： 出现如图出现如图11.711.7所示的消息，提示插入的记录出错。再打所示的消息，提示插入的记录出错。再打开开studentstudent表，从中看到，由于进行了事务回滚，所以并不表，从中看到，由于进行了事务回滚，所以并不会真正向会真正向studentstudent表中插入学号为表中插入学号为502502的新记录。的新记录。USE s

32、choolUSE schoolGOGOINSERT INTO student(INSERT INTO student(学号学号, ,姓名姓名, ,性别性别) ) VALUES(502,VALUES(502,王丽王丽,女女)GOGO 【例例11.811.8】 建立一个触发器建立一个触发器trigsextrigsex，当向，当向studentstudent表中表中插入数据时，如果出现性别不正确的情况，不回滚该事务，只插入数据时，如果出现性别不正确的情况，不回滚该事务，只提示错误消息。提示错误消息。USE schoolUSE schoolGOGOCREATE TRIGGER CREATE TRIGG

33、ER trigsextrigsex- -创建创建trigsextrigsex触发器触发器 ON student AFTER INSERT ON student AFTER INSERTASAS DECLARE DECLARE s1s1 char(1) char(1) SELECT SELECT s1s1= =性别性别 FROM INSERTED FROM INSERTED IF IF s1s1男男 OR OR s1s1女女 RAISERRORRAISERROR( (性别只能取男或女性别只能取男或女,16,1),16,1)- -发出一条错误消息发出一条错误消息GOGO当执行以下程序当执行以下程序

34、： 出现如图出现如图11.811.8所示的消息，提示插入的记录出错。再打所示的消息，提示插入的记录出错。再打开开studentstudent表，从中看到，由于没有进行事务回滚，尽管要插表，从中看到，由于没有进行事务回滚，尽管要插入的记录不正确，但仍然插入到入的记录不正确，但仍然插入到studentstudent表中了，如图表中了，如图11.911.9所所示。示。USE schoolUSE schoolGOGOINSERT student VALUES(503,INSERT student VALUES(503,许涛许涛,M,1992M,1992-10-16,1005)-10-16,1005)G

35、OGO2. 2. 应用应用UPDATEUPDATE触发器触发器 可将可将UPDATEUPDATE语句看成两步操作：即捕获数据前像的语句看成两步操作：即捕获数据前像的DELETEDELETE语句，和捕获数据后像的语句，和捕获数据后像的INSERTINSERT语句。当在定义有触语句。当在定义有触发器的表上执行发器的表上执行UPDATEUPDATE语句时，原始行（前像）被移入到语句时，原始行（前像）被移入到deleteddeleted表，更新行（后像）被移入到表，更新行（后像）被移入到insertedinserted表。表。 触发器检查触发器检查deleteddeleted表和表和insertedi

36、nserted表以及被更新的表，表以及被更新的表，来确定是否更新了多行以及如何执行触发器动作。来确定是否更新了多行以及如何执行触发器动作。 可以使用可以使用IF UPDATEIF UPDATE语句定义一个监视指定列的数据更语句定义一个监视指定列的数据更新的触发器。新的触发器。 【例例11.911.9】 建立一个更新触发器建立一个更新触发器trignotrigno，该触发器防止，该触发器防止用户修改表用户修改表studentstudent的学号。的学号。USE schoolUSE schoolGOGOCREATE TRIGGER CREATE TRIGGER trignotrigno-创建创建t

37、rignotrigno触发器触发器ON studentON studentAFTER UPDATEAFTER UPDATEASASIF UPDATE(IF UPDATE(学号学号) )BEGINBEGINRAISERRORRAISERROR(不能修改学号不能修改学号,16,2),16,2)ROLLBACKROLLBACKENDENDGOGO当当执行以下程序执行以下程序：USE schoolUSE schoolGOGOUPDATE student SET UPDATE student SET 学号学号=301 WHERE =301 WHERE 学号学号=101=101GOGO 出现如图出现如图1

38、1.1011.10所示的消息，提示修改记录时出错，也并所示的消息，提示修改记录时出错，也并没有修改没有修改studentstudent表中学号为表中学号为“101101”的记录。的记录。 【例例11.1011.10】建立一个触发器建立一个触发器trigcopytrigcopy，将，将studentstudent表中表中所有被修改的数据保存到所有被修改的数据保存到stbakstbak表中作为历史记录。表中作为历史记录。USE schoolUSE schoolGOGOIF IF OBJECT_IDOBJECT_ID( (stbak,Ustbak,U) IS NOT NULL) IS NOT NUL

39、LDROP TABLE DROP TABLE stbakstbak- -若存在若存在stbakstbak表，删除之表，删除之CREATE TABLE CREATE TABLE stbakstbak- -创建创建stbakstbak表表( (rqrq datetimedatetime, ,- -修改时间修改时间snosno char(10), char(10),- -学号学号snamesname char(10), char(10),- -姓名姓名ssexssex char(2), char(2),- -性别性别sbirthdaysbirthday datetimedatetime, ,- -出

40、生日期出生日期sclasssclass char(10) char(10)- -班号班号) )GOGOCREATE TRIGGER CREATE TRIGGER trigcopytrigcopy-创建触发器创建触发器trigcopytrigcopyON student AFTER UPDATEON student AFTER UPDATEASAS-将当前日期和修改后的记录插入到将当前日期和修改后的记录插入到stbakstbak表中表中INSERT INTO INSERT INTO stbakstbak( (rq,sno,sname,ssex,sbirthday,sclassrq,sno,sna

41、me,ssex,sbirthday,sclass) )SELECT SELECT getdategetdate(),inserted.(),inserted.学号学号,inserted.,inserted.姓名姓名, ,inserted.inserted.性别性别,inserted.,inserted.出生日期出生日期,inserted.,inserted.班号班号 FROM FROM student,insertedstudent,insertedWHERE student.WHERE student.学号学号=inserted.=inserted.学号学号GOGO执行以下程序：执行以下程序

42、：USE schoolUSE schoolGOGOUPDATE student SET UPDATE student SET 班号班号=2001 WHERE =2001 WHERE 班号班号=1001=1001-修改班号修改班号GOGOUPDATE student SET UPDATE student SET 班号班号=1001 WHERE =1001 WHERE 班号班号=2001 =2001 -恢复班号恢复班号GOGO 该程序两次修改该程序两次修改studentstudent表中的班号，表中的班号，studentstudent表中的记录表中的记录恢复成修改前的状态，而恢复成修改前的状态，而

43、stbakstbak表中的记录如图表中的记录如图11.1111.11所示，从所示，从中看到每次修改中看到每次修改studentstudent表时都将修改情况保存到表时都将修改情况保存到stbakstbak表中了。表中了。3. 3. 应用应用DELETEDELETE触发器触发器 当触发当触发DELETEDELETE触发器后，从受影响的表中删除的行将被触发器后，从受影响的表中删除的行将被放置到放置到deleteddeleted表中。表中。deleteddeleted表保留已被删除数据行的一个表保留已被删除数据行的一个副本。副本。deleteddeleted表还允许引用由初始化表还允许引用由初始化D

44、ELETEDELETE语句产生的日语句产生的日志数据。志数据。 使用使用DELETEDELETE触发器时，需要注意：当某行被添加到触发器时，需要注意：当某行被添加到deleteddeleted表中时，它就不再存在于数据库表中，因此，表中时，它就不再存在于数据库表中，因此，deleteddeleted表和数据库表没有相同的行。表和数据库表没有相同的行。 【例例11.1111.11】建立一个删除触发器建立一个删除触发器trigclasstrigclass，该触发，该触发器防止用户删除表器防止用户删除表studentstudent中所有中所有10011001班的学生记录。班的学生记录。USE sch

45、oolUSE schoolGOGOCREATE TRIGGER CREATE TRIGGER trigclasstrigclass-创建触发器创建触发器trigsclasstrigsclassON student AFTER DELETEON student AFTER DELETEASAS IF EXISTS(SELECT IF EXISTS(SELECT * * FROM deleted WHERE FROM deleted WHERE 班号班号=1001)=1001) BEGIN BEGIN RAISERRORRAISERROR(不能删除不能删除10011001班的学生记录班的学生记录,

46、16,2),16,2) ROLLBACK ROLLBACK END ENDGOGO执行以下程序：执行以下程序：USE schoolUSE schoolGOGODELETE student WHERE DELETE student WHERE 班号班号=1001=1001GOGO 出现如图出现如图11.1211.12所示的消息，提示修改记录时出错。所示的消息，提示修改记录时出错。由于存在事务回滚，由于存在事务回滚，studentstudent表中的数据保持不变。表中的数据保持不变。 【例例11.1211.12】建立一个触发器建立一个触发器trigcopy1trigcopy1，将，将student

47、student表中表中所有被删除记录的学号保存到所有被删除记录的学号保存到stbakstbak表中作为历史记录。表中作为历史记录。USE schoolUSE schoolGOGOIF IF OBJECT_IDOBJECT_ID(stbak,Ustbak,U) IS NOT NULL) IS NOT NULLDROP TABLE DROP TABLE stbakstbak-若存在若存在stbakstbak表，删除之表，删除之CREATE TABLE CREATE TABLE stbakstbak-创建创建stbakstbak表表( (rqrq datetimedatetime, ,-删除时间删除

48、时间snosno char(10), char(10),-学号学号snamesname char(10), char(10),-姓名姓名ssexssex char(2), char(2),-性别性别sbirthdaysbirthday datetimedatetime, ,-出生日期出生日期sclasssclass char(10) char(10)-班号班号) )GOGOCREATE TRIGGER CREATE TRIGGER trigcopy1trigcopy1-创建触发器创建触发器trigcopy1trigcopy1 ON student AFTER DELETE ON student

49、 AFTER DELETE ASAS BEGIN BEGIN - -将当前日期和被删除的记录插入到将当前日期和被删除的记录插入到stbakstbak表中表中INSERT INTO INSERT INTO stbakstbak( (rq,sno,sname,ssex,sbirthday,sclassrq,sno,sname,ssex,sbirthday,sclass) ) SELECT SELECT getdategetdate(),deleted.(),deleted.学号学号,deleted.,deleted.姓名姓名, ,deleted.deleted.性别性别,deleted.,dele

50、ted.出生日期出生日期,deleted.,deleted.班号班号FROM FROM student,deletedstudent,deleted END ENDGOGO执行以下程序：执行以下程序：USE schoolUSE schoolGOGODELETE studentDELETE student-删除删除10031003班的学生记录班的学生记录WHERE WHERE 班号班号=1003=1003GOGO 执行上述程序，删除执行上述程序，删除studentstudent表中班号为表中班号为“10031003”的的记录的同时，将这些删除的记录存放到记录的同时，将这些删除的记录存放到stba

51、kstbak表中。表中。11.2.6 INSTEAD OF11.2.6 INSTEAD OF触发器触发器 INSTEAD INSTEAD OFOF 触发器用来代替通常的触发动作，即当对触发器用来代替通常的触发动作，即当对表进行表进行INSERTINSERT、UPDATEUPDATE 或或 DELETEDELETE 操作时，系统不是直接操作时，系统不是直接对表执行这些操作，而是把操作内容交给触发器，让触发对表执行这些操作，而是把操作内容交给触发器，让触发器检查所进行的操作是否正确，如正确才进行相应的操作。器检查所进行的操作是否正确，如正确才进行相应的操作。 通俗地讲，对数据库的操作只是一个通俗地

52、讲，对数据库的操作只是一个“导火线导火线”而已，而已，真正起作用的是真正起作用的是INSTEADINSTEAD OFOF触发器触发器里面的动作。因触发器触发器里面的动作。因此，此，INSTEADINSTEAD OFOF 触发器的动作要早于表的约束处理，所以触发器的动作要早于表的约束处理，所以采用触发器，能定义比完整性约束更加复杂的约束。采用触发器，能定义比完整性约束更加复杂的约束。 【例例11.1311.13】在在scorescore表上创建一个表上创建一个INSTEAD OF INSERTINSTEAD OF INSERT触触发器发器trigscoretrigscore，当用户插入成绩记录时

53、检查学号是否在，当用户插入成绩记录时检查学号是否在studentstudent表中。表中。USE schoolUSE schoolGOGOCREATE TRIGGER CREATE TRIGGER trigscoretrigscore ON score ON score INSTEAD OF INSERTINSTEAD OF INSERTASASIF NOT EXISTS(SELECT IF NOT EXISTS(SELECT * * FROM student FROM student WHERE WHERE 学号学号=(SELECT =(SELECT 学号学号 FROM inserted)

54、FROM inserted) BEGIN BEGIN ROLLBACK TRANSACTION ROLLBACK TRANSACTION PRINT PRINT 要处理记录的学号不存在！要处理记录的学号不存在！ END END ELSE ELSE BEGIN BEGIN INSERT INTO score SELECT INSERT INTO score SELECT * * FROM inserted FROM inserted PRINT PRINT 已经成功处理记录！已经成功处理记录！ END END执行以下程序：执行以下程序：USE schoolUSE schoolGOGOINSERT

55、 score VALUES(205,3-105,90)INSERT score VALUES(205,3-105,90)GOGO 由于由于studentstudent表中不存在学号表中不存在学号“205205”，所有出现如图，所有出现如图11.1311.13所示的结果。所示的结果。 从结果看到，当向从结果看到，当向scorescore表中插入记录时，自动执行表中插入记录时，自动执行trigscoretrigscore触发器，用其中的触发器，用其中的T-SQLT-SQL语句替代该插入语句，这样语句替代该插入语句，这样被插入的记录并没有实际插入到被插入的记录并没有实际插入到scorescore表中

56、。表中。 【例例11.1411.14】在在scorescore表上创建一个表上创建一个INSTEAD OF INSERTINSTEAD OF INSERT触发触发器器trigscore1trigscore1，当用户修改时，不允许修改学号，其他情况显，当用户修改时，不允许修改学号，其他情况显示修改结果。示修改结果。USE schoolUSE schoolGOGOIF IF OBJECT_IDOBJECT_ID(trigscore1,TRtrigscore1,TR) IS NOT NULL) IS NOT NULL DROP TRIGGER DROP TRIGGER trigscore1trigs

57、core1GOGOCREATE TRIGGER CREATE TRIGGER trigscore1trigscore1 ON score ON score INSTEAD OF UPDATEINSTEAD OF UPDATEASASBEGINBEGIN IF UPDATE( IF UPDATE(学号学号) ) BEGIN BEGIN ROLLBACK TRANSACTION ROLLBACK TRANSACTION PRINT PRINT 不能修改学号！不能修改学号！ END END ELSE ELSE BEGIN BEGIN DECLARE DECLARE xhxh char(3), cha

58、r(3),kchkch char(5), char(5),kch1kch1 char(5), char(5), fsfs char(2), char(2),fs1fs1 char(2) char(2) SELECT SELECT xhxh= =学号学号,kchkch= =课程号课程号,fsfs= =分数分数 from deleted from deleted SELECT SELECT kch1kch1= =课程号课程号,fs1fs1= =分数分数 from inserted from inserted UPDATE score UPDATE score SET SET 课程号课程号=kch1

59、kch1, ,分数分数=fs1fs1 WHERE WHERE 学号学号=xhxh AND AND 课程号课程号=kchkch PRINT PRINT 学生学生+xhxh+由由+kchkch+课程课程+fsfs+分数修改为分数修改为+kch1kch1+课程课程+fs1fs1+分数分数 END ENDENDEND执行以下程序：执行以下程序：USE schoolUSE schoolGOGOUPDATE scoreUPDATE scoreSET SET 课程号课程号=3-105,=3-105,分数分数=85=85WHERE WHERE 学号学号=101 AND =101 AND 课程号课程号=3-10

60、5=3-105GOGO 当执行当执行UPDATEUPDATE命令时，转向执行命令时，转向执行trigscore1trigscore1触发器，触发器，获取修改前后的数据，通过获取修改前后的数据，通过UPDATEUPDATE命令做真正的修改，并命令做真正的修改，并显示修改结果，其显示的消息如图显示修改结果，其显示的消息如图11.1411.14所示。所示。 【例例11.1511.15】在在testtest数据库中建立数据库中建立table21table21和和table22table22两个两个表，并插入若干记录。在表，并插入若干记录。在table21table21表上创建一个表上创建一个INSTE