单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,第四章 触发器,【部分】,触发器概,触发器的概念,是一种特殊的存储过程，在指定标的数据发生,变化时自动生效。,具体地讲，就是当对,某一表,进行数据插入（,insert,）、删除（,delete,）以及更改（,update,）操作时，自动执行做某些处理的SQL 语句。,第四章 触发器【部分】触发器概,1,触发器的特点,特殊的存储过程,特殊点是不需要由用户维护。当对表作,UPDATE、INSERT、DELETE,时自动触发执行。,保证业务规则和完整性。,触发器的特点特殊的存储过程,2,触发器应用场合,完成比CHECK约束更复杂的数据约束。,为保证数据库性能而维护的,非规范化数据,。,实现复杂的商业规则。,评估数据修改前后的表状态，并根据其差异采取对策。,一个表中的多个同类触发器（INSERT、UPDATE 或 DELETE）允许采取多个不同的对策以响应同一个修改语句。,触发器应用场合完成比CHECK约束更复杂的数据约束。,3,触发器创建时包含的4个核心要素,指定触发器的名称。,指出触发器所作用的表。,指出引发触发器的操作：,INSERT、UPDATE 或 DELETE,多个或者其中的一个。,编写SQL语句，实现触发器中腰完成的工作。,触发器创建时包含的4个核心要素指定触发器的名称。,4,8.1 创建触发器,CREATE TRIGGER,触发器名称,ON,表名|视图名,WITH ENCRYPTION,FOR|AFTER|INSTEAD OF,INSERT,DELETE,UPDATE,AS,IF,UPDATE,(,column,),AND|OR UPDATE(column).,SQL 语句,8.1 创建触发器CREATE TRIGGER 触发器名称,5,创建触发器注意事项,WITH ENCRYPTION：加密，不看看到原来的SQL脚本,FOR 或AFTER类型【后触发】,：所有SQL语句都执行完且执行成功的情况下才能执行触发器。,可以在同一种操作上建立多个触发器；,INSTEAD OF类型【替代触发】,：仅执行触发器本身，而不执行引起触发操作的SQL语句。,在同一种操作上只能建立一个触发器。,在一个表上可以建立多个名称不同、类型各异的触发器，每个触发器可由所有三个操作来引发。,大部分Transact-SQL语句都可用在触发器中，但所有的建立和,更改数据库以及数据库对象的语句,、,所有的DROP语句,都不允许在触发器中使用。,在触发器定义中，可以使用IF UPDATE子句来测试在INSERT和UPDATE语句中是否对指定字段有影响。,通常不在触发器中返回任何结果。,在触发器中可使用特殊的二个表：INSERTED、DELETED,创建触发器注意事项WITH ENCRYPTION：加密，不看,6,inserted 表和 deleted 表,在触发器执行时，会产生2个临时表：,inserted,表,deleted,表,inserted,表:用于存储 insert 和 update 语句所影响的副本,delete,表:用于存储 delete 和 update 语句所影响的副本,inserted 表和 deleted 表,7,INSERTED表和DELETED表 说明,INSERTED和DELETED是与,操作表结构,一样的临时表，其驻留内存，可用于测试修改结果等，不能直接修改，只能在触发器语句中使用。,INSERTED保存,新插入,的数据和,UPDATE后,的数据副本。,DELETED保存,删除的数据,和UPDATE,更改前,数据副本。,在用户执行数据的更改操作时，SQL Server自动创建和管理,驻留在内存中。,结构同触发器所作用的基本表的结构。,只可以被触发器使用。,由系统维护，在触发器中像普通表一样查询，不能直接对这两个临时表中的数据进行更改。,INSERTED表和DELETED表 说明INSERTED和,8,删除、插入、更改后数据副本的保存,在执行,DELETE操作,时，被删除的数据被保存到DELETED表中。它和执行操作的基本表没有相同的行。,在执行,INSERT操作,时，新插入的数据同时被保存到INSERTED表中。基本表中新数据行的副本。,在执行,UPDATE操作,时，先从触发器表中删除旧行，然后再插入新行。因此，将更改前的数据保存到DELETED表中。将更改后的数据行保存到INSERTED表。,删除、插入、更改后数据副本的保存在执行DELETE操作时，被,9,8.2 后触发型触发器,使用,FOR或AFTER,选项定义的触发器为,后触发的,触发器，即,只有在引发触发器执行的语句中指定的操作都已成功执行,，并且,所有的约束检查也成功完成后,，才执行触发器。,注意：不能在视图上定义AFTER触发器。,8.2 后触发型触发器 使用FOR或AFTER选项定义的触发,10,CREATE TABLE Student,(,Sno CHAR(9)PRIMARY KEY,Sname CHAR(20),Ssex CHAR(2),Sage int ,Sdept CHAR(20),),建立一个“学生选课”表SC,CREATE TABLE SC,(,Sno CHAR(9),Cno CHAR(4),Grade int,),CREATE TABLE Student,11,例1：创建如下触发器，在插入、修改和删除记录时，自动显示表中内容,create trigger,studenttrig1,on,student,for insert,update,delete,as,select *from student,执行如下语句，激发触发器,insert into student values(0000a,张三,男,19,cs),update student set sname=张五 where sno=0000a,例1：创建如下触发器，在插入、修改和删除记录时，自动显示表中,12,例2：下例说明 inserted 表和 deleted 表的作用,create trigger student _trig2 on student,for insert,update,delete,as,print inserted表,select*from inserted,print deleted表,select*from deleted,执行触发语句：,执行触发语句：,insert student values(00ab,pppp,男,20,cs),update student set sage=21 where sno=00ab,delete student where sno=00ab,例2：下例说明 inserted 表和 deleted 表的,13,1.insert 和 update触发器,这两种触发器用来检查插入或者修改后的数据是否满足要求,例3，创建触发器，检查插入记录的学生年龄是否在18-20之间,create trigger student_trig3 on student,for insert,update,as,declare s_age int,select s_age=sage from inserted,if s_age 20,begin,rollback,print(年龄值必须在18-20),end,执行触发语句：,insert student values(0044,张户,女,23,cs),select*from student,insert student values(0033,赵强,女,20,cs),update student set sage=30 where sno=0033,1.insert 和 update触发器,14,2.delete 触发器,delete触发器通常用于下面的情况：,在删除数据记录时，保持数据的一致性,级联删除操作,例4，关于学生表和成绩表的数据删除,delete student,delete sc,insert student values(01,aaaa,男,19,sc),insert sc values(01,1,90),insert sc values(01,2,96),select*from student,select*from sc,创建触发器,create trigger student_tr1 on student,for delete,as,delete sc where sc.sno=(select sno from deleted),执行触发语句：,delete student where sno=01,2.delete 触发器,15,3.触发器的其它应用,例5，在student 表上创建一个禁止删除和插入操作的触发器,create trigger student_tr1 on student for,insert,update,as,begin,print 不允许你插入或删除任何数据！,rollback transaction,return,end,执行触发语句：,insert student values(003,aa,女,20,cs),例6，在student表上创建一个禁止修改主键操作的触发器,create trigger student_tr2 on student,for update,as,if update(sno),begin,print 不能修改学号,rollback,end,执行触发语句：,update student set sno=001 where sname=张力,3.触发器的其它应用,16,4.嵌套触发器的应用,嵌套触发器指一个触发器触发后，执行了某个动作，该动作又触发了第二个触发器，以此类推，最多可32级。,例6，创建一个级联删除操作的触发器,create trigger student_tr1 on student,for delete,as,delete sc where sc.sno=(select sno from deleted),create trigger sc_tr1 on sc,for delete,as,print 该生的成绩信息均已删除！,执行触发语句：,delete student where sno=01,4.嵌套触发器的应用,17,5.,instead of,触发器的应用,instead off,指定,执行触发器,而不是,执行触发语句,。,【先删除所做触发器】,例7，创建一个,instead off,触发器，执行触发器，不执行触发语句,create trigger t1 on sc,instead of insert,as,insert sc values(001,01,90),执行以下语句：,insert into sc values(xxx,99,79),在表中插入的数据记录是：(001,01,90),而不是：(xxx,99,79),5.instead of 触发器的应用,18,例8创建限制删除表t1中c2列中值为“a”的记录的触发器。,【避免删除超级用户，很有意义】,CREATE TRIGGER tri3,ON t1,INSTEAD OF,delete,as,if not exists(select*from deleted,where c2=a),delete from t1,where c2 in(select c2 from deleted),例8创建限制删除表t1中c2列中值为“a”的记录的触发器。,19,触发器的查看、修改与删除,1.触发器的查看,.,使用系统存储过程：sp_helptext 触发器名,.,使用企业管理器,2.触发器的修改,.,使用 alter trigger 语句,.,使用企业管理器,3.触发器的删除,.,使用 drop trigger 语句,.,使用企业管理器,触发器的查看、修改与删除,20,