《高级数据库》课程教学课件（讲稿）Chapter 7 触发器

Chapter7触发器触发器是一个被指定关联到一个表的数据对象，触发器是不需要调用的，当对一个表的特别事件出现时，它就会被激活。触发器与表的关系密切，用于保护表中的数据，当有操作影响到触发器保护的数据时，触发器将自动执行。7.1触发器的类型在SQLServer2008中，按照触发事件的不同可以将触发器分为两大类：DML触发器和DDL触发器。DML触发器。当数据库中发生数据操纵语言（DML）事件时将调用DML触发器。DML事-件包括对表或视图的INSERT语句、UPDATE语句和DELETE语句，因而DML触发器也可分为三种类型：INSERT、UPDATE和DELETE。利用DML触发器可以方便地保持数据库中数据的完整性和一致性。-DDL触发器。DDL触发器也是由相应的事件触发的，但DDL触发器触发的事件是数据定义语句（DDL）。这些语句主要是以CREATE、ALTER、DROP等关键字开头的语句。DDL触发器的主要作用是执行管理操作，例如审核系统、控制数据库（防止对数据库架构进行修改等）的操作。7.2创建DML触发器使用CREATETRIGGER语句创建DML触发器，其语法格式如下：CREATETRIGGERschemanameJtrigger_nameON(table/view)/*指定操作对象*AFTERINSTEADOF)([INSERT ][,][UPDATE][,][DELETE ]]ASsq_statement[;][...n]语法说明：CREATETRIGGER语句必须是批处理中的第一条语句，并且只能应用到一个表中。图-trigger_name用于指定触发器名。一table/view指在其上执行触发器的表或视图。■AFTER用于说明触发器在指定操作都成功执行后触发，如AFTERINSERT表示向表中插入数据时激活触发器。不能在视图上定义AFTER触发器。■INSTEADOF指定用DML触发器中的操作代替触发语句的操作。在表或视图上，每个INSERT/UPDATE/DELETE语句最多可以定义一个INSTEADOF触发器。■［INSERT][，][UPDATE]】[，][DELETE]】指定激活触发器的语句类型，必须至少指定一个选项。INSERT表示将新行插入表时激活触发器，UPDATE表示更改某一行时激活触发器，DELETE表示从表中删除某一行时激活触发器。■sql_statement触发器的T-SQL语句，可以有一条或多条语句，指定DML触发器激活后

Chapter 7 触发器 触发器是一个被指定关联到一个表的数据对象，触发器是不需要调用的，当对一个表的 特别事件出现时，它就会被激活。触发器与表的关系密切，用于保护表中的数据，当有操作 影响到触发器保护的数据时，触发器将自动执行。 触发器的类型 在 SQL Server 2008 中，按照触发事件的不同可以将触发器分为两大类：DML 触发器和 DDL 触发器。 ◼ DML 触发器。当数据库中发生数据操纵语言（DML）事件时将调用 DML 触发器。DML 事 件包括对表或视图的 INSERT 语句、UPDATE 语句和 DELETE 语句，因而 DML 触发器也可 分为三种类型：INSERT、UPDATE 和 DELETE。 利用 DML 触发器可以方便地保持数据库中数据的完整性和一致性。 ◼ DDL 触发器。DDL 触发器也是由相应的事件触发的，但 DDL 触发器触发的事件是数据定 义语句（DDL）。这些语句主要是以 CREATE、ALTER、DROP 等关键字开头的语句。DDL 触发器的主要作用是执行管理操作，例如审核系统、控制数据库（防止对数据库架构进行修 改等）的操作。 创建 DML 触发器 使用 CREATE TRIGGER 语句创建 DML 触发器，其语法格式如下： CREATE TRIGGER [ schema_name . ]trigger_name ON { table | view } /*指定操作对象*/ { AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } AS sql_statement [ ; ] [ .n ] 语法说明： ◼ CREATE TRIGGER 语句必须是批处理中的第一条语句，并且只能应用到一个表中。 ◼ trigger_name 用于指定触发器名。 ◼ table | view 指在其上执行触发器的表或视图。 ◼ AFTER 用于说明触发器在指定操作都成功执行后触发，如 AFTER INSERT 表示向表 中插入数据时激活触发器。不能在视图上定义 AFTER 触发器。 ◼ INSTEAD OF 指定用 DML 触发器中的操作代替触发语句的操作。在表或视图上，每个 INSERT/UPDATE/DELETE 语句最多可以定义一个 INSTEAD OF 触发器。 ◼ { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } 指定激活触发器的语句类型，必须至少 指定一个选项。INSERT 表示将新行插入表时激活触发器，UPDATE 表示更改某一行时 激活触发器，DELETE 表示从表中删除某一行时激活触发器。 ◼ sql_statement 触发器的 T-SQL 语句，可以有一条或多条语句，指定 DML 触发器激活后

将要执行的动作。DML触发器最大的用途是返回行级数据的完整性，而不是返回结果所以应当尽量避免返回任何结果集。执行触发器时，系统创建了两个特殊的临时表inserted和deleted。inserted表：当向表中插入数据时，INSERT触发器触发执行，新的记录插入到触发器表和inserted表中。deleted表：当一个DELETE触发器触发执行时，被删除的记录存放到deleted表中。-1更新/修改一条记录等同于插入一条新纪录，同时删除旧记录。当对定义了UPDATE触发器的表记录进行修改时，表中原记录移到deleted表中，修改过的记录插入到inserted表中。由于inserted表和deleted表都是临时表，它们在触发器执行时被创建，触发器执行完就消失了，所以只可以在触发器的语句中使用SELECT语句查询这两个表。7.2.1INSERT触发器INSERT触发器是当对触发器表执行INSERT语句时就会激活的触发器。【例7-1】创建触发器TR_1，当向CJB表插入某学生成绩，若成绩合格，在XSB中该生的总学分加上该门课程的学分。IF EXISTS(SELECT * FROM sysobjects WHERE name-'TR 1' and type-'TR')DROP TRIGGER TR1GOCREATE TRIGGER TR 1ONCJBAFTER INSERTAS BEGINDECLARE @cj int,@xf int,@id char(6),@cid char(3)SELECT @id-学号，@cid-课程编号，@cj-成绩FROM insertedSET @xf=（SELECTKCB.学分FROMKCBWHERE KCB.课程编号-@cid）IF@cj>=60UPDATE XSBSETXSB.总学分+=@XfWHERE XSB.学号-@idENDGO接着向CJB表插入一行数据，并查看触发器执行结果（通过INSERT语句前后的SELECT语句来查看CJB和XSB的变化，此处由于涉及数据的更新，使用回滚事务）。BEGIN TRANSELECT * FROM CJBSELECT*FROMXSBINSERT INTO CJBVALUES(*081101,209',80)SELECT *FROM CJBSELECT*FROM XSBROLLBACK

将要执行的动作。DML 触发器最大的用途是返回行级数据的完整性，而不是返回结果， 所以应当尽量避免返回任何结果集。 执行触发器时，系统创建了两个特殊的临时表 inserted 和 deleted。 ◼ inserted 表：当向表中插入数据时，INSERT 触发器触发执行，新的记录插入到触发器 表和 inserted 表中。 ◼ deleted 表：当一个 DELETE 触发器触发执行时，被删除的记录存放到 deleted 表中。 ◼ 更新/修改一条记录等同于插入一条新纪录，同时删除旧记录。当对定义了 UPDATE 触 发器的表记录进行修改时，表中原记录移到 deleted 表中，修改过的记录插入到 inserted 表中。 由于 inserted 表和 deleted 表都是临时表，它们在触发器执行时被创建，触发器执行完就 消失了，所以只可以在触发器的语句中使用 SELECT 语句查询这两个表。 INSERT 触发器 INSERT 触发器是当对触发器表执行 INSERT 语句时就会激活的触发器。 【例 7-1】创建触发器 TR_1，当向 CJB 表插入某学生成绩，若成绩合格，在 XSB 中该生的 总学分加上该门课程的学分。 IF EXISTS(SELECT * FROM sysobjects WHERE name='TR_1' and type='TR') DROP TRIGGER TR_1 GO CREATE TRIGGER TR_1 ON CJB AFTER INSERT AS BEGIN DECLARE @cj int, @xf int, @id char(6),@cid char(3) SELECT @id=学号,@cid=课程编号,@cj=成绩 FROM inserted SET @xf=(SELECT KCB.学分 FROM KCB WHERE KCB.课程编号=@cid) IF @cj>=60 UPDATE XSB SET XSB.总学分+=@xf WHERE XSB.学号=@id END GO 接着向CJB表插入一行数据，并查看触发器执行结果（通过INSERT语句前后的SELECT 语句来查看 CJB 和 XSB 的变化，此处由于涉及数据的更新，使用回滚事务）。 BEGIN TRAN SELECT * FROM CJB SELECT * FROM XSB INSERT INTO CJB VALUES ('081101','209',80) SELECT * FROM CJB SELECT * FROM XSB ROLLBACK

7.2.2DELETE触发器【例7-2】创建触发器TR2，在删除XSB表中的一条学生记录时将CJB表中该学生的相应记录也删除。IFEXISTS(SELECT*FROMSysobjects WHERE name='TR_2'andtype=‘TR")DROPTRIGGERTR2GOCREATE TRIGGER TR 2ON XSBAFTER DELETEAS BEGINDECLARE @id char(6)SET@id=(SELECT 学号 FROM deleted)DELETE FROM CJBWHERE CJB.学号-@idENDGO接着删除XSB表中的一行数据，并查看触发器执行结果BEGIN TRANDELETE FROMXSB WHERE学号-081101SELECT*FROMXSBSELECT*FROM CJBROLLBACK如果此处改为删除XSB表中女生数据，则会报错，原因在于触发器中该语句SET @id=（SELECT 学号 FROM deleted因此，为了能够使触发器更具可扩展性和使用性，往往在触发器中使用游标来处理多行结果集。TR2可做如下修改，修改后的触发器即支持删除XSB表中单条记录，又支持删除多条记录。IF EXISTS(SELECT *FROM sySobjectsWHERE name='TR 2and type-'TR')DROPTRIGGER TR2GOCREATE TRIGGER TR 2ON XSBAFTER DELETEAS BEGINDECLARE @id char(6)--SET@id=(SELECT学号FROMdeleted)--设计一个从deleted表中获取学号的游标DECLARE Cur 1 CURSORFORSELECT学号FROMdeletedOPEN cur_1FETCH NEXT FROM Cur 1 INTO @idWHILE @@FETCH STATUS-O

DELETE 触发器 【例 7-2】创建触发器 TR_2，在删除 XSB 表中的一条学生记录时将 CJB 表中该学生的相应 记录也删除。 IF EXISTS(SELECT * FROM sysobjects WHERE name='TR_2' and type='TR') DROP TRIGGER TR_2 GO CREATE TRIGGER TR_2 ON XSB AFTER DELETE AS BEGIN DECLARE @id char(6) SET @id=(SELECT 学号 FROM deleted) DELETE FROM CJB WHERE CJB.学号=@id END GO 接着删除 XSB 表中的一行数据，并查看触发器执行结果： BEGIN TRAN DELETE FROM XSB WHERE 学号='081101' SELECT * FROM XSB SELECT * FROM CJB ROLLBACK 如果此处改为删除 XSB 表中女生数据，则会报错，原因在于触发器中该语句： SET @id=(SELECT 学号 FROM deleted) 因此，为了能够使触发器更具可扩展性和使用性，往往在触发器中使用游标来处理多行 结果集。TR_2 可做如下修改，修改后的触发器即支持删除 XSB 表中单条记录，又支持删除 多条记录。 IF EXISTS(SELECT * FROM sysobjects WHERE name='TR_2' and type='TR') DROP TRIGGER TR_2 GO CREATE TRIGGER TR_2 ON XSB AFTER DELETE AS BEGIN DECLARE @id char(6) -SET @id=(SELECT 学号FROM deleted) -设计一个从deleted表中获取学号的游标 DECLARE cur_1 CURSOR FOR SELECT 学号 FROM deleted OPEN cur_1 FETCH NEXT FROM cur_1 INTO @id WHILE @@FETCH_STATUS=0

BEGINDELETE FROM CJB WHERE CJB.学号-@idFETCH NEXT FROMCur1INTO @idENDCLOSE cur_1DEALLOCATE Cur_1ENDGO【例7-3】创建触发器TR3，当取消某门课程（KCB）时，应同时在成绩表CJB中删除该课程记录，并在学生表XSB中减去选修该门课程学生的学分。IF EXISTS(SELECT * FROM sysobjects WHERE name-'TR 3' and type-'TR')DROP TRIGGER TR3GOCREATE TRIGGER TR 3ON KCBAFTER DELETEAS BEGINDECLARE @id char(6)@cid char(3),@xf intSET @cid=（SELECT 课程编号 FROM deleted）SET@xf=（SELECT学分FROMdeleted)--设计一个获取选修@cid课程学生学号的游标DECLARE Cur 1 CURSORFOR SELECTCJB.学号FROMCJB WHERECJB.课程编号-@CidOPEN cur_1FETCH NEXTTFROMcur1INTO@idWHILE @@FETCH STATUS-OBEGIN--在学生表XSB中减去学分UPDATE XSBSETXSB.总学分-=@xfWHERE XSB.学号-@id--读取下一个学生学号FETCH NEXT FROMCur1INTO @idENDCLOSE cur_1DEALLOCATE Cur1--在成绩表CJB中删除该课程记录DELETE CJBWHERE CJB.课程编号-@cidENDGO接着删除课程101的选课记录，并查看触发器执行结果：

BEGIN DELETE FROM CJB WHERE CJB.学号=@id FETCH NEXT FROM cur_1 INTO @id END CLOSE cur_1 DEALLOCATE cur_1 END GO 【例 7-3】创建触发器 TR_3，当取消某门课程（KCB）时，应同时在成绩表 CJB 中删除该 课程记录，并在学生表 XSB 中减去选修该门课程学生的学分。 IF EXISTS(SELECT * FROM sysobjects WHERE name='TR_3' and type='TR') DROP TRIGGER TR_3 GO CREATE TRIGGER TR_3 ON KCB AFTER DELETE AS BEGIN DECLARE @id char(6),@cid char(3),@xf int SET @cid=(SELECT 课程编号 FROM deleted) SET @xf=(SELECT 学分 FROM deleted) -设计一个获取选修@cid课程学生学号的游标 DECLARE cur_1 CURSOR FOR SELECT CJB.学号 FROM CJB WHERE CJB.课程编号=@cid OPEN cur_1 FETCH NEXT FROM cur_1 INTO @id WHILE @@FETCH_STATUS=0 BEGIN -在学生表XSB中减去学分 UPDATE XSB SET XSB.总学分-=@xf WHERE XSB.学号=@id -读取下一个学生学号 FETCH NEXT FROM cur_1 INTO @id END CLOSE cur_1 DEALLOCATE cur_1 -在成绩表CJB中删除该课程记录 DELETE CJB WHERE CJB.课程编号=@cid END GO 接着删除课程 101 的选课记录，并查看触发器执行结果：

BEGIN TRANFROM CJB WHERE CJB.课程编号-101!SELECT★SELECT*FROMXSBDELETE KCB WHERE KCB.课程编号=101SELECT *FROM CJBSELECT *FROMXSBROLLBACK7.2.3UPDATE触发器UPDATE触发器在对触发器表执行UPDATE语句后触发。在执行UPDATE触发器时将触发器表的原记录保存到deleted临时表中，将修改后的记录保存到inserted临时表中。【例7-4】创建触发器TR4，当修改XSB表中的学号时，同时也要将CJB表中的学号修改成相应的学号（假设XSB表和CJB表之间没有定义外键约束）。IF EXISTS(SELECT * FROM sysobjects WHERE name='TR_4'and type-'TR')DROP TRIGGER TR4GOCREATE TRIGGER TR 4ON XSBAFTER UPDATEAS BEGINDECLARE @new idchar(6),@old id char(6)SET @new id-(SELECT学号 FROMinserted)SET@oldid=(SELECT学号FROMdeleted)IF@newid<>@oldid--如果没有该条件限定，所有对于xSB的UPDATE语句都会执行以下操作：UPDATE CJBSET CJB.学号-@new_idWHERECJB.学号-@old idENDGO接着修改XSB表中的一行数据，并查看触发器执行结果BEGIN TRANUPDATE XSBSET 学号-091101!WHERE 学号-081101SELECT*FROMXSBSELECT *FROM CJBROLLBACK7.2.4INSTEADOF触发器AFTER触发器使在触发语句执行后触发的，与AFTER触发器不同的是，INSTEADOF触发器触发时只执行触发器内部的SQL语句，而不执行激活该触发器的语句。一个表或视图只能有一个INSTEADOF触发器

BEGIN TRAN SELECT * FROM CJB WHERE CJB.课程编号='101' SELECT * FROM XSB DELETE KCB WHERE KCB.课程编号='101' SELECT * FROM CJB SELECT * FROM XSB ROLLBACK UPDATE 触发器 UPDATE 触发器在对触发器表执行 UPDATE 语句后触发。在执行 UPDATE 触发器时， 将触发器表的原记录保存到 deleted 临时表中，将修改后的记录保存到 inserted 临时表中。 【例 7-4】创建触发器 TR_4，当修改 XSB 表中的学号时，同时也要将 CJB 表中的学号修改 成相应的学号（假设 XSB 表和 CJB 表之间没有定义外键约束）。 IF EXISTS(SELECT * FROM sysobjects WHERE name='TR_4' and type='TR') DROP TRIGGER TR_4 GO CREATE TRIGGER TR_4 ON XSB AFTER UPDATE AS BEGIN DECLARE @new_id char(6),@old_id char(6) SET @new_id=(SELECT 学号 FROM inserted) SET @old_id=(SELECT 学号 FROM deleted) IF @new_id<>@old_id -如果没有该条件限定，所有对于XSB的UPDATE语句都会执 行以下操作. UPDATE CJB SET CJB.学号=@new_id WHERE CJB.学号=@old_id END GO 接着修改 XSB 表中的一行数据，并查看触发器执行结果： BEGIN TRAN UPDATE XSB SET 学号='091101' WHERE 学号='081101' SELECT * FROM XSB SELECT * FROM CJB ROLLBACK INSTEAD OF 触发器 AFTER 触发器使在触发语句执行后触发的，与 AFTER 触发器不同的是，INSTEAD OF 触发器触发时只执行触发器内部的 SQL 语句，而不执行激活该触发器的语句。一个表或视 图只能有一个 INSTEAD OF 触发器

【例7-5】创建触发器TR5，删除XSB中某学生（若选课过不删除，没选课过则删除）。IF EXISTS (SELECT *FROM sysobjects WHERE name-'TR_5'ANDtype='TR')DROP TRIGGER TR5GOCREATE TRIGGER TR 5ONXSBINSTEADOF DELETEASBEGINDECLARE @id char(6)SET @id=(SELECT 学号FROMdeleted)IF EXISTS (SELECT *FROM CJB WHERE学号-@id)PRINT该学生已选过课不能删除ELSEBEGINDELETE FROM XSB WHERE 学号-@idPRINT该学生已成功删除ENDENDGO接着删除XSB表中081101学生（已选过课），并查看触发器执行结果BEGIN TRANDELETE FROM XSB WHERE 学号=1081101!ROLLBACK接着删除XSB表中081206学生（未选过课），并查看触发器执行结果BEGIN TRANDELETE FROMXSBWHERE 学号-'081206ROLLBACKINSTEADOF触发器的一个很大的作用是使不可更新的视图支持更新。如果视图的数据来自于多个基表，则必须使用INSTEADOF触发器支持引用表中数据的插入、更新和删除操作。【例7-6】在PXSCJ数据库中创建视图VieW1，包含学生学号、姓名、课程编号以及成绩。该视图依赖于表XSB和CJB，是不可更新视图。若在该视图上创建INSTEADOF触发器当向视图中插入数据时，可以实现分别向XSB和CJB基表中插入数据的功能。USE PXSCJGOCREATE VIEW VIEW1ASSELECTXSB.学号，姓名，课程编号，成绩FROMXSB,CJB

【例 7-5】创建触发器 TR_5，删除 XSB 中某学生（若选课过不删除，没选课过则删除）。 IF EXISTS (SELECT * FROM sysobjects WHERE name='TR_5' AND type='TR') DROP TRIGGER TR_5 GO CREATE TRIGGER TR_5 ON XSB INSTEAD OF DELETE AS BEGIN DECLARE @id char(6) SET @id=(SELECT 学号 FROM deleted) IF EXISTS (SELECT * FROM CJB WHERE 学号=@id) PRINT '该学生已选过课不能删除' ELSE BEGIN DELETE FROM XSB WHERE 学号=@id PRINT '该学生已成功删除' END END GO 接着删除 XSB 表中 081101 学生（已选过课），并查看触发器执行结果： BEGIN TRAN DELETE FROM XSB WHERE 学号='081101' ROLLBACK 接着删除 XSB 表中 081206 学生（未选过课），并查看触发器执行结果： BEGIN TRAN DELETE FROM XSB WHERE 学号='081206' ROLLBACK INSTEAD OF 触发器的一个很大的作用是使不可更新的视图支持更新。如果视图的数 据来自于多个基表，则必须使用 INSTEAD OF 触发器支持引用表中数据的插入、更新和删 除操作。 【例 7-6】在 PXSCJ 数据库中创建视图 View_1，包含学生学号、姓名、课程编号以及成绩。 该视图依赖于表 XSB 和 CJB，是不可更新视图。若在该视图上创建 INSTEAD OF 触发器， 当向视图中插入数据时，可以实现分别向 XSB 和 CJB 基表中插入数据的功能。 USE PXSCJ GO CREATE VIEW VIEW_1 AS SELECT XSB.学号,姓名,课程编号,成绩 FROM XSB,CJB

WHERE XSB.学号-CJB.学号GO接着创建一个与视图关联的INSTEADOF触发器TR_6，实现通过视图向基表插入数据的功能。IF EXISTS(SELECTT * FRoM sysobjects.Ename=TR_6iANDWHEREtype="TR')DROP TRIGGER TR6GOCREATE TRIGGER TR 6ON VIEW 1INSTEAD OF INSERTAS BEGINDECLARE @id char(6),@name char(8),@cid char(3),@cj intSELEcT @id-学号，@name=姓名，@cid-课程编号，@cj-成绩FROMinsertedIF NOT EXISTS(SELECT *FROM XSB WHERE XSB.学号-@id)INSERT INTOXSB（学号，姓名）VALUES(@id,@name)IFNOT EXISTS（SELECT*FROMCJB WHERECJB.学号-@idANDCJB.课程编号-@cid)INSERT INTO CJBVALUEs(@id,@cid,@cj)ENDGO最后向视图VIEW1插入数据，并查看触发器执行结果。BEGIN TRANINSERTINTOVIEW 1VALUES（081102，程明102，80）INSERT INTO VIEW1VALUES（081234，张三"，301",60)SELECT*1FROM XSBSELECT *FROMCJBSELECT★FROMVIEW1ROLLBACK

WHERE XSB.学号=CJB.学号 GO 接着创建一个与视图关联的 INSTEAD OF 触发器 TR_6，实现通过视图向基表插入数 据的功能。 IF EXISTS(SELECT * FROM sysobjects WHERE name='TR_6' AND type='TR') DROP TRIGGER TR_6 GO CREATE TRIGGER TR_6 ON VIEW_1 INSTEAD OF INSERT AS BEGIN DECLARE @id char(6), @name char(8), @cid char(3), @cj int SELECT @id=学号,@name=姓名,@cid=课程编号,@cj=成绩 FROM inserted IF NOT EXISTS(SELECT * FROM XSB WHERE XSB.学号=@id) INSERT INTO XSB(学号,姓名) VALUES (@id, @name) IF NOT EXISTS (SELECT * FROM CJB WHERE CJB.学号=@id AND CJB.课程 编号=@cid) INSERT INTO CJB VALUES(@id,@cid,@cj) END GO 最后向视图 VIEW_1 插入数据，并查看触发器执行结果。 BEGIN TRAN INSERT INTO VIEW_1 VALUES ('081102','程明','102',80) INSERT INTO VIEW_1 VALUES ('081234','张三','301',60) SELECT * FROM XSB SELECT * FROM CJB SELECT * FROM VIEW_1 ROLLBACK