数据库原理与应用(15)第15章触发器

1、第,15,章,触发器,15.1,概,述,15.1.1,触发器的概念,触发器是一种特殊类型的存储过程，它在插入、删除或,修改指定表中的数据时触发执行。触发器通常可以强制执行,一定的业务规则，以保持数据完整性、检查数据有效性、实,现数据库管理任务和一些附加的功能。,触发器具有如下优点：,?,触发器是被自动执行的，不需要显式调用。,?,触发器可以调用存储过程。,?,触发器可以强化数据条件约束。,?,触发器可以禁止或回滚违反引用完整性的数据,修改或删除。,?,利用触发器可以进行数据处理。,?,触发器可以级联、并行执行。,?,在同一个表中可以设计多个触发器。,15.1.2,触发器的种类,SQL Serv

2、er 2005,提供了两种类型的触发器：,?,DML,触发器,：在执行数据操作语言事件时被调用的触发,器，其中数据操作语言事件包括：,INSERT,、,UPDATE,和,DELETE,语句。触发器中可以包含复杂的,T-SQL,语句，,触发器整体被看作一个事务，可以进行回滚。,?,DDL,触发器,：与,DML,触发器类似，它由相应的事件触发,后执行。与,DML,不同的是，它的触发事件是由数据定义,语言引起的事件，包括,CREATE,、,ALTER,和,DROP,语句。,DDL,触发器用于执行数据库管理任务，如调节和审计数,据库运转。,DDL,触发器只能在触发事件发生后才会调用,执行，即它只能是,

3、AFTER,类型的。,15.2,创建,DML,触发器,15.2.1,使用,SQL Server,管理控制器创建,DML,触发器,通过一个简单的示例说明使用,SQL Server,管理控制器创建,触发器的操作步骤。,【例,15.1,】,使用,SQL Server,管理控制器在,student,表上创建,一个触发器,trigop,，其功能是在用户插入、修改或删除该表中,行中输出所有的行。,解：,其操作步骤如下：,（,1,）启动,SQL Server,管理控制器，在“对象资源管理器”,中展开“,LCB-,PC”,服务器节点。,（,2,）展开“数据库”,|“school”|“,表”,|“student

4、”|“,触发器”,节点，单击鼠标右键，在出现的快捷菜单中选择“新建触发器”,命令。,（,3,）出现一个新建触发器编辑窗口，其中包含触发器模板，,用户可以参照模板在其中输入触发器的,T-SQL,语句，这里输入,的语句如下（其中红字部分为主要输入的,T-SQL,语句）：,SET ANSI_NULLS ON,GO,SET QUOTED_IDENTIFIER ON,GO,CREATE TRIGGER trigop,ON student AFTER INSERT,DELETE,UPDATE,AS,BEGIN,SET NOCOUNT ON,SELECT * FROM student,END,GO,（,4,

5、）单击工具栏中的“执行”按钮，将该触发器保存到,相关的系统表中。这样就创建了触发器,trigop,。,在触发器,trigop,创建完毕，当对,student,表进行记录插入、修改,或删除操作时，触发器,trigop,都会被自动执行。例如，执行以,下程序：,USE school,INSERT student V,ALUES(1,刘明,男,1991-12-12,1035),GO,当向,student,表中插入一个记录时自动执行触发器,trigop,输,出其所有记录，输出结果如图,15.1,所示，从中看到新记录已经,插入到,student,表中了。,15.2.2,使用,T-SQL,语句创建,DML,

6、触发器,创建,DML,触发器可以使用,CREATE TRIGGER,语句，其基,本语法格式如下：,CREATE TRIGGER,触发器名称,ON ,表名,|,视图名,WITH ENCRYPTION, FOR | AFTER | INSTEAD OF INSERT , UPDATE,WITH APPEND,NOT FOR REPLICATION,AS, IF UPDATE ( column ), AND | OR UPDATE ( column ),n,| IF (COLUMNS_UPDATED() bitwise_operator updated_bitmask ), comparison_o

7、perator column_bitmask n, ,SQL,语句,n ,【例,15.2,】,在数据库,test,中建立一个表,table10,，创建一个,触发器,trigtest,，在,table10,表中插入、修改和删除记录时，自动,显示表中的所有记录。并用相关数据进行测试。,解：,创建表和触发器的语句如下：,USE test,GO,CREATE TABLE table10,-,创建表,table10,(,c1 int,c2 char(30),),GO,CREATE TRIGGER trigtest,-,创建触发器,trigtest,ON table10 AFTER INSERT,UPDA

8、TE,DELETE,AS,SELECT * FROM table10,GO,在执行下面的语句时：,USE test,INSERT Table10 V,ALUES(1,Name1),GO,结果会显示出,table10,表中的行如图,15.2,所示。,在执行下面的语句时：,USE test,UPDATE Table10 SET c2=Name2 WHERE c1=1,GO,结果会显示出,table10,表中的记录行如图,15.3,所示。,15.3 inserted,表和,deleted,表,在触发器执行的时候，会产生两个临时表：,inserted,表和,deleted,表。它们的结构和触发器所在的

9、表的结构相同。,deleted,表用于存储,DELETE,和,UPDATE,语句所影响的行,的副本。在执行,DELETE,或,UPDATE,语句时，行从触发器表,中删除，并传输到,deleted,表中。,deleted,表和触发器表通常没,有相同的行。,inserted,表用于存储,INSERT,和,UPDATE,语句所影响的行,的副本。在一个插入或更新事务处理中，新建行被同时添加,到,inserted,表和触发器表中。,inserted,表中的行是触发器表中,新行的副本。,在对具有触发器的表（简称为触发器表）进行操作时，,其操作过程如下：,?,执行,INSERT,操作，插入到触发器的表中的新

10、行被插,入到,inserted,表中。,?,执行,DELETE,操作，从触发器表中删除的行被插入,到,deleted,表中。,?,执行,UPDATE,操作，先从触发器表中删除旧行，然,后再插入新行。其中被删除的旧行被插入到,deleted,表中，插入的新行被插入到,inserted,表中。,【例,15.3,】,编写一段,T-SQL,语句说明,inserted,表和,deleted,表的作用。,解：,创建触发器,trigtest,的语句如下：,USE test,GO,IF EXISTS(SELECT * FROM sysobjects WHERE name=table10 AND,type=U)

11、,DELETE table10,-,若存在,table10,表，则删除其记录,GO,IF EXISTS(SELECT * FROM sysobjects WHERE name=trigtest AND,type=TR),DROP TRIGGER trigtest,-,若存在,trigtest,触发器，则删除之,GO,CREATE TRIGGER trigtest,-,创建触发器,trigtest,ON table10 AFTER INSERT,UPDATE,DELETE,AS,PRINT inserted,表,:,SELECT * FROM inserted,PRINT deleted,表,:

12、,SELECT * FROM deleted,GO,如果此时执行下面的,INSERT,语句：,USE test,INSERT table10 V,ALUES(2,Name3),GO,其执行结果如图,15.4,所示。,如果此时接着执行下面的,UPDATE,语句：,USE test,UPDATE table10 SET c2=Name4 WHERE c1=2,GO,其执行结果如图,15.5,所示。,如果此时接着执行下面的,DELETE,语句：,USE test,DELETE table10 WHERE c1=2,GO,其执行结果如如图,15.6,所示。,15.4,使用,DML,触发器,15.4.1

13、,使用,INSERT,触发器,INSERT,触发器通常被用来更新时间标记字段，或者验证,被触发器监控的字段中数据满足要求的标准，以确保数据的完,整性。当向数据库中插入数据时，,INSERT,触发器将被触发执,行。,INSERT,触发器被触发时，新的记录增加到触发器的对应,表中，并且同时也添加到一个,inserted,表中。该,inserted,表是一,个逻辑表，以确定该触发器的操作是否应该执行，以及如何去,执行。,【例,15.4,】,建立一个触发器,trigname,，当向,student,表中插,入数据时，如果出现姓名重复的情况，则回滚该事务。,解：,创建触发器,trigname,的程序如下

14、：,USE school,GO,CREATE TRIGGER trigname,-,创建,trigname,触发器,ON student AFTER INSERT,AS,BEGIN,DECLARE name char(10),SELECT name=inserted.,姓名,FROM inserted,IF EXISTS(SELECT,姓名,FROM student WHERE,姓名,=name),BEGIN,RAISERROR(,姓名重复,不能插入,16,1),ROLLBACK,-,事务回滚,END,END,执行以下程序：,USE school,INSERT INTO student(,学号

15、,姓名,性别,) V,ALUES(102,王丽,女,),GO,出现如图,15.7,所示的消息，提示插入的记录出错。,再打开,student,表，从中看到，由于进行了事务回滚，所,以并不会真正向,student,表中插入学号为,102,的新记录。,【例,15.5,】,建立一个触发器,trigsex,，当向,student,表中插入,数据时，如果出现性别不正确的情况，不回滚该事务，只提示,错误消息。,解：,创建触发器,trignsex,的程序如下：,USE school,GO,CREATE TRIGGER trigsex,-,创建,trigsex,触发器,ON student AFTER INSE

16、RT,AS,DECLARE s1 char(1),SELECT s1=,性别,FROM INSERTED,IF s1,男, OR s1,女,RAISERROR(,性别只能取男或女,16,1),-,发出一条错误消息,GO,当执行以下程序：,USE school,INSERT student V,ALUES(2,许涛,M,1992-10-16,1035),GO,出现如图,15.8,所示的消息，提示插入的记录出错。,再打开,student,表，从中看到，由于没有进行事务回滚，,尽管要插入的记录不正确，但仍然插入到,student,表中了，,其中学号为,1,的记录是在例,15.1,中插入的。,15.4

17、.2,使用,UPDATE,触发器,修改触发器和插入触发器的工作过程基本上一致，修改一,条记录等于插入了一个新的记录并且删除一个旧的记录。,当在一个有,UPDATE,触发器的表中修改记录时，表中原来,的记录被移动到,deleted,表中，修改过的记录插入到了插入表中，,触发器可以参考,deleted,表和,inserted,表以及被修改的表，以确,定如何完成数据库操作。,【例,15.6,】,建立一个修改触发器,trigno,，该触发器防止用,户修改表,student,的学号。,解：,创建触发器,trignno,的程序如下：,USE school,GO,CREATE TRIGGER trigno,

18、-,创建,trigno,触发器,ON student,AFTER UPDATE,AS,IF UPDATE(,学号,),BEGIN,RAISERROR(,不能修改学号,16,2),ROLLBACK,图,15.10,执行触发器,trigno,时提示的消息,END,GO,当执行以下程序：,USE school,UPDATE student,SET,学号,=3,WHERE,学号,=1,GO,出现如图,15.10,所示的消息，提示修改记录时出错，也并,没有修改,student,表中学号为,1,的记录。,【例,15.7,】,建立一个触发器,trigcopy,，将,student,表中所有,被修改的数据保存

19、到,stbak,表中作为历史记录。,解：,创建触发器,trigcopy,的程序如下：,USE school,GO,-,若存在,stbak,表，删除之,否则创建表,stbak,IF EXISTS(SELECT name FROM sysobjects WHERE name=stbak,AND type=U),DROP TABLE stbak,CREATE TABLE stbak,-,创建,stbak,表,(,rq datetime,-,修改时间,sno char(10),-,学号,sname char(10),-,姓名,ssex char(2),-,性别,sbirthday datetime,-

20、,出生日期,sclass char(10),-,班号,),GO,CREATE TRIGGER trigcopy,-,创建触发器,trigcopy,ON student AFTER UPDATE,AS,-,将当前日期和修改后的记录插入到,stbak,表中,INSERT INTO stbak(rq,sno,sname,ssex,sbirthday,sclass),SELECT getdate(),inserted.,学号,inserted.,姓名,inserted.,性别,inserted.,出生日期,inserted.,班号,FROM student,inserted,WHERE student

21、.,学号,=inserted.,学号,GO,执行以下程序：,USE school,-,修改班号,UPDATE student,SET,班号,=1131,WHERE,班号,=1031,GO,-,恢复班号,UPDATE student,SET,班号,=1031,WHERE,班号,=1131,GO,执行上述程序，两次修改,student,表中的班号，,student,表中,的记录恢复成修改前的状态，而,stbak,表中的记录如图,15.11,所,示，,15.4.3,使用,DELETE,触发器,当执行,DELETE,触发器时，表中原来的记录被移动到,deleted,表中。,DELETE,触发器通常用于

22、为了防止删除一些不能删除的,数据和实现数据表级联操作等情况。,【例,15.8,】,建立一个删除触发器,trigclass,，该触发器防止,用户删除表,student,中所有,1031,班的学生记录。,解：,创建触发器,trigclass,的程序如下：,USE school,GO,CREATE TRIGGER trigclass,-,创建触发器,trigsclass,ON student,AFTER DELETE,AS,IF EXISTS(SELECT * FROM deleted WHERE,班号,=1031),BEGIN,RAISERROR(,不能删除,1031,班的学生记录,16,2),R

23、OLLBACK,END,GO,执行以下程序：,USE school,DELETE student,WHERE,班号,=1031,GO,在事务回滚，,出现如图,15.12,student,所示的消息，提示修改记录时出错。由于存,表中的数据保持不变。,15.4.4,使用,INSTEAD OF,触发器,执行,INSTEAD OF,触发器是为了替换那些初始化触发器,的修改语句。,下面通过几个例子说明,AFTER,触发器和,INSTEAD OF,触,发器的差别。,【例,15.10,】,在,teacher,表上创建一个,INSTEAD OF INSERT,触发器,trigteacher,，当用户插入数据时

24、显示,teacher,表中所有数,据。,解：,创建触发器,trigteacher,的程序如下：,USE school,GO,CREATE TRIGGER trigteacher,-,创建触发器,trigteacher,ON teacher INSTEAD OF INSERT,AS,SELECT * FROM teacher,GO,执行以下程序：,USE school,INSERT INTO teacher(,编号,) V,ALUES(688),GO,出现如图,15.13,所示的结果。,从结果看到，当向,teacher,表中插入记录时，自动执行,trigteacher,触发器，用其中的,SELE

25、CT,语句替代该插入语句，,这样被插入的记录并没有插入到,teacher,表中。这就是,INSERT,触发器与,INSTEAD OF INSERT,触发器的区别。,15.5,创建和使用,DDL,触发器,DML,触发器属表级触发器，而,DDL,触发器属数据库级触,发器。像,DML,触发器一样，,DDL,触发器也是被自动执行，但,与,DML,触发器不同的是，它们不是响应表或视图的,INSERT,、,UPDATE,或,DELETE,等记录操作语句，而是响应数据定义语,句（,DDL,）操作，这些语句以,CREATE,、,ALTER,和,DROP,开,头的语句。,DDL,触发器可用于管理任务，例如，审核

26、和控制数据库,操作。,15.5.1,创建,DDL,触发器,使用,CREATE TRIGGER,命令创建,DDL,触发器的基本语,法格式如下。,CREATE TRIGGER,触发器名称,ON ALL SERVER|DATABASE,FOR|AFTER event_type|event_group,n,AS SQL,语句,15.5.2 DDL,触发器的应用,在响应当前数据库或服务器中处理的,T-SQL,事件时，可以,激发,DDL,触发器。触发器的作用域取决于事件。,例如，每当数据库中发生,CREATE TABLE,事件时，都会触,发为响应,CREATE TABLE,事件创建的,DDL,触发器。每当

27、服务,器中发生,CREATE LOGIN,事件时，都会触发为响应,CREATE,LOGIN,事件创建的,DDL,触发器。,【例,15.11,】,在,school,数据库上创建一个,DDL,触发器,safe,，,用来防止该数据库中的任一表被修改或删除。,解：,创建,DDL,触发器的程序如下：,USE school,GO,CREATE TRIGGER safe,-,创建触发器,safe,ON DATABASE AFTER DROP_TABLE,ALTER_TABLE,AS,BEGIN,RAISERROR(,不能修改表结构,16,2),ROLLBACK,END,GO,当执行以下程序：,USE sch

28、ool,ALTER TABLE student ADD,民族,char(10),GO,出现如图,15.14,所示的消息，提示修改,student,表结构时出错，,而且,student,表结构保持不变。,15.6,触发器的管理,15.6.1,查看触发器,数据库中创建的每个触发器在,sys.triggers,表中对应一个记,录，例如，为了显示本章前面在,school,数据库上创建的触发器，,可以使用以下程序：,其执行结果如图,15.16,所示，其中，,DDL,触发器的,parent_class,列为,0,。,USE school,SELECT * FROM sys.triggers,1.,使用,S

29、QL Server,管理控制台查看触发器,通过一个简单的示例说明使用,SQL Server,管理控制器查看,触发器的操作步骤。,【例,15.13,】,使用,SQL Server,管理控制器查看,student,表上,的触发器,trigop,（在例,15.1,中创建）。,解：,其操作步骤如下：,（,1,）启动,SQL Server,管理控制器，在“对象资源管理器”,中展开“,LCB-,PC”,服务器节点。,（,2,）展开“数据库”,|“school”|“,表”,|“student”|“,触发,器”,|“trigop”,节点，单击鼠标右键，在出现的快捷菜单中选择,“编写触发器脚本为,|CREATE,到,|,新查询编辑器窗口”命令。,（,3,）出现如图,15.17,所示的,trigop,触发器编辑窗口，用户,可以在其中查看,trigop,触发器的源代码。,2.,使用系统存储过程查看触发器,系统存储过程,sp_help,、,sp_helptext,和,sp_depends,分别提,供有关触发器的不同信息（这些系统存储过程仅适合于,DML,触发器）。,（,1,）,sp_help,用于查看触发器