第13章触发器原理及使用

1、 触发器是一种特殊类型的存储过程，触发器是一种特殊类型的存储过程，它在用户使用它在用户使用UPDATEUPDATE、INSERTINSERT和和DELETEDELETE语句修改关系中的数据时触发执行。语句修改关系中的数据时触发执行。第第1313章章 触发器原理及使用触发器原理及使用 113.1 触发器基本概念触发器基本概念13.2 触发器原理触发器原理13.3 触发器的创建和管理触发器的创建和管理13.4 使用触发器实现强制业务规则使用触发器实现强制业务规则13.5 使用触发器的使用触发器的T-SQL限制限制13.6 触发器应用实例分析触发器应用实例分析213.1 13.1 触发器基本概念触发

2、器基本概念 13.1.1 13.1.1 触发器的概念及作用触发器的概念及作用触发器可以看作是一类特殊的存储过程，它在满足某个特定触发器可以看作是一类特殊的存储过程，它在满足某个特定条件时自动触发执行。条件时自动触发执行。 触发器是为表上的更新、插入、删除操作触发器是为表上的更新、插入、删除操作定义的，也就是说当表上发生更新、插入或删除操作时触发器将定义的，也就是说当表上发生更新、插入或删除操作时触发器将执行。存储过程和触发器同是提高数据库服务器性能的有力工具。执行。存储过程和触发器同是提高数据库服务器性能的有力工具。触发器作为一种特殊类型的存储过程，它不同于我们前面介触发器作为一种特殊类型的存

3、储过程，它不同于我们前面介绍过的存储过程。触发器主要是通过事件进行触发而被执行的，绍过的存储过程。触发器主要是通过事件进行触发而被执行的，而存储过程可以通过存储过程名字而被直接调用。当对某一表进而存储过程可以通过存储过程名字而被直接调用。当对某一表进行诸如行诸如UPDATE、INSERT、DELETE这些操作时，这些操作时，SQL Server就会自动执行触发器所定义的就会自动执行触发器所定义的SQL语句，从而确保对数据的处理语句，从而确保对数据的处理必须符合由这些必须符合由这些SQL语句所定义的规则。语句所定义的规则。 3触发器还有其它许多不同的功能：触发器还有其它许多不同的功能：（1）强化

4、约束（）强化约束（Enforce restriction）触发器可以侦测数据库内的操作，从而不允许数据库中未经许触发器可以侦测数据库内的操作，从而不允许数据库中未经许可的更新和变化。可的更新和变化。（2）级联运行（）级联运行（Cascaded Operation）触发器可以侦测数据库内的操作，并自动地级联影响整个数据触发器可以侦测数据库内的操作，并自动地级联影响整个数据库的各项内容。例如，某个表上的触发器包含对另外一个表的库的各项内容。例如，某个表上的触发器包含对另外一个表的数据操作（如删除、更新、插入），而该操作又导致该表上的数据操作（如删除、更新、插入），而该操作又导致该表上的触发器被触发

5、。触发器被触发。（3）存储过程的调用（）存储过程的调用（Stored Procedure Invocation）为了响应）为了响应数据库更新，触发器可以调用一个或多个存储过程，甚至可以数据库更新，触发器可以调用一个或多个存储过程，甚至可以通过外部过程的调用而在通过外部过程的调用而在DBMS之外进行操作。之外进行操作。4 13.1.2 13.1.2 触发器的种类触发器的种类 SQL Server 2000支持两种类型的触发器：支持两种类型的触发器：AFTER触发器和触发器和INSTEAD OF触发器。触发器。 （1）AFTER触发器触发器 即为即为SQL Server 2000版本以前所介绍的触

6、发器。该类型触发器要版本以前所介绍的触发器。该类型触发器要求只有执行完某一操作（求只有执行完某一操作（INSERT、UPDATE、DELETE），并），并处理过所有约束后，触发器才被触发，且只能在表上定义。如果处理过所有约束后，触发器才被触发，且只能在表上定义。如果操作违反约束条件，将导致事务回滚，这时就不会执行后触发器。操作违反约束条件，将导致事务回滚，这时就不会执行后触发器。513.1.2 13.1.2 触发器的种类触发器的种类 （2）INSTEAD OF触发器触发器 该类触发器表示并不执行其所定义的操作（该类触发器表示并不执行其所定义的操作（INSERT、UPDATE、DELETE），而

7、仅是执行触发器本身。既可在表上定义），而仅是执行触发器本身。既可在表上定义INSTEAD OF触发器，也可以在视图上定义触发器，也可以在视图上定义INSTEAD OF触发器，触发器，但对同一操作只能定义一个但对同一操作只能定义一个INSTEAD OF触发器。触发器。613.2 13.2 触发器原理触发器原理 从以上的介绍中我们可以已了解到触发器具有强大从以上的介绍中我们可以已了解到触发器具有强大的功能，那么的功能，那么MS SQL Server 是如何使得触发器能够感是如何使得触发器能够感知数据库数据的变化、维护数据库参照完整性及比知数据库数据的变化、维护数据库参照完整性及比CHECK约束更复

8、杂的约束呢？下面我们将对其工作原约束更复杂的约束呢？下面我们将对其工作原理及实现做较为详细的介绍，以便大家学习创建、理解理及实现做较为详细的介绍，以便大家学习创建、理解和使用各种类型的触发器，完成各种任务。和使用各种类型的触发器，完成各种任务。713.2.1 13.2.1 插入表的功能插入表的功能对一个定义了插入类型触发器的表来讲，一对一个定义了插入类型触发器的表来讲，一旦对该表执行了插入（旦对该表执行了插入（INSERT）操作，那么对该）操作，那么对该表插入的所有行来说，都有一个相应的副本级存表插入的所有行来说，都有一个相应的副本级存放到插入表（放到插入表（inserted）中，即插入表就是

9、用来存）中，即插入表就是用来存储原表插入的新数据行。储原表插入的新数据行。813.2.2 13.2.2 删除表的功能删除表的功能 对一个定义了删除类型触发器的表来讲，一旦对该对一个定义了删除类型触发器的表来讲，一旦对该表执行了删除（表执行了删除（DELETE）操作，则将所有的被删）操作，则将所有的被删除的行存放至删除表（除的行存放至删除表（deleted表）中。这样做的目表）中。这样做的目的是，一旦触发器遇到了强迫它中止的语句被执行的是，一旦触发器遇到了强迫它中止的语句被执行时，删除的那些行可以从删除表（时，删除的那些行可以从删除表（deleted表）中得表）中得以还原。以还原。 9更新（UP

10、DATE）操作 需要强调的是，更新（需要强调的是，更新（UPDATE）操作包括两个）操作包括两个部分，即先将旧的内容删除，然后将新值插入。部分，即先将旧的内容删除，然后将新值插入。因此，对一个定义了更新类型触发器的表来讲，因此，对一个定义了更新类型触发器的表来讲，当执行更新操作时，在删除表中存放了修改之前当执行更新操作时，在删除表中存放了修改之前的旧值，然后在插入表中存放的是修改之后的新的旧值，然后在插入表中存放的是修改之后的新值。值。1013.2.3 13.2.3 插入视图和删除视图插入视图和删除视图 当在定义了触发器的表上发生修改操作时会自当在定义了触发器的表上发生修改操作时会自动派生出两

11、个视图，一个是插入视图，一个是删除视动派生出两个视图，一个是插入视图，一个是删除视图。当在表上发生插入操作时，新插入的行将出现在图。当在表上发生插入操作时，新插入的行将出现在inserted表中形成插入视图；当在表上发生删除操作表中形成插入视图；当在表上发生删除操作时，被删除的旧行将出现时，被删除的旧行将出现deleted表中，形成删除视图。表中，形成删除视图。而更新的实现过程是先删除旧行，然后再插入新行。而更新的实现过程是先删除旧行，然后再插入新行。1113.3.1 13.3.1 创建触发器创建触发器 上面介绍了有关触发器的概念、作用和工作上面介绍了有关触发器的概念、作用和工作原理，下面我们

12、将分别介绍在原理，下面我们将分别介绍在MS SQLServer 中如中如何用何用SQL Server管理工具管理工具Enterprise Manager和和Transaction_SQL来创建触发器。来创建触发器。13.3 13.3 触发器的创建和管理触发器的创建和管理121用对象资源管理器创建触发器用对象资源管理器创建触发器步骤如下：步骤如下： 启动对象资源管理器，登录到要使用的服务器。启动对象资源管理器，登录到要使用的服务器。 在对象资源管理器的左窗格中，展开要创建触发器的数据库文在对象资源管理器的左窗格中，展开要创建触发器的数据库文件夹，单击件夹，单击“表表”文件夹前面的文件夹前面的“+

13、”号，此时在右窗格中显号，此时在右窗格中显示该数据库的所有表。示该数据库的所有表。 选择创建触发器的表，单击要创建触发器的数据表前面的选择创建触发器的表，单击要创建触发器的数据表前面的“+”号，右击触发器选项，在出现的下一级子菜单中选择号，右击触发器选项，在出现的下一级子菜单中选择“新建触新建触发器发器”菜单项。菜单项。 132 2用用CREATE TRIGGERCREATE TRIGGER命令创建触发器命令创建触发器语法格式：语法格式： CREATE TRIGGER trigger_name ON table | view WITH ENCRYPTION FOR | AFTER | INST

14、EAD OF INSERT , UPDATE WITH APPEND NOT FOR REPLICATION 14AS IF UPDATE ( column ) AND | OR UPDATE ( column ) .n | IF ( COLUMNS_UPDATED ( ) bitwise_operator updated_bitmask ) comparison_operator column_bitmask .n Sql 语句语句 .n 15【例13-1】创建一个触发器，当向STUDENT表中插入一条学生记录时，自动显示该表中的记录。USE 教学管理GOCREATE TRIGGER T_学

15、生表改变显示 On 学生表FOR INSERTASBEGINIF (COLUMNS_UPDATED()&1=1) SELECT * FROM 学生表END-验证 BEGIN TRANSACTIONINSERT INTO 学生表VALUES(S090103, *19971021*, 李飞, 男, 130*12, 温州, 计算机, 信电学院, 160)ROLLBACK TRANSACTION 该触发器建立完毕后，当对学生表的插入操作执行成功时，将会显示学生表中的全部记录。1613.3.2 13.3.2 管理触发器管理触发器1使用对象资源管理器显示触发器信息使用对象资源管理器显示触发器信息步

16、骤如下。步骤如下。（1）启动对象资源管理器，登录到要使用的服务器。）启动对象资源管理器，登录到要使用的服务器。 （2）在对象资源管理器的左窗格中，展开要创建触）在对象资源管理器的左窗格中，展开要创建触发器的数据库文件夹，单击发器的数据库文件夹，单击“表表”文件夹前面的文件夹前面的“+”号，在下面显示该数据库的所有表。号，在下面显示该数据库的所有表。 （3）单击要修改触发器的数据表前面的）单击要修改触发器的数据表前面的“+”号，出号，出现触发器选项，再单击触发器前面的现触发器选项，再单击触发器前面的“+”号，下面号，下面显示该表上建立的所有触发器，将鼠标指向要修改的显示该表上建立的所有触发器，将

17、鼠标指向要修改的触发器，单击右键，在出现的下一级子菜单中选择触发器，单击右键，在出现的下一级子菜单中选择“修改修改”菜单项，就会出现修改该触发器的文本框菜单项，就会出现修改该触发器的文本框.172使用系统存储过程查看触发器使用系统存储过程查看触发器（1）sp_help通过该系统过程，可以了解触发器的一般信息，如触发通过该系统过程，可以了解触发器的一般信息，如触发器的名字、属性、类型、创建时间。使用器的名字、属性、类型、创建时间。使用sp_help系系统过程的命令格式为：统过程的命令格式为： sp_help 触发器名字触发器名字【例【例13-2】查看我们已经建立的】查看我们已经建立的“T_表改变

18、显示表改变显示”触发器。触发器。 sp_help T_表改变显示表改变显示 18（2）sp_helptext通过通过sp_helptext查看触发器的正文信息，其语法格式为：查看触发器的正文信息，其语法格式为： sp_helptext 触发器名触发器名【例【例13-3】查看我们已经建立的】查看我们已经建立的chang_display触发器的触发器的命令文本。命令文本。 sp_helptext T_表改变显示表改变显示 （3）sp_depends通过通过sp_depends查看指定触发器所引用的表或指定的表查看指定触发器所引用的表或指定的表所涉及到的所有触发器。其语法形式如下：所涉及到的所有触发

19、器。其语法形式如下： sp_depends 触发器名字触发器名字 sp_depends 表名表名【例【例13-4】查看】查看T_表改变显示表改变显示触发器所涉及的表。触发器所涉及的表。 sp_depends T_表改变显示表改变显示 2使用系统存储过程查看触发器使用系统存储过程查看触发器1913.3.3 13.3.3 修改、删除触发器修改、删除触发器1修改触发器修改触发器（1）使用）使用sp_rename命令，修改触发器的名字命令，修改触发器的名字sp_rename命令的语法格式为：命令的语法格式为：sp_rename oldname, newname20（2）使用）使用Alert trigg

20、er，修改触发器的正文，修改触发器的正文 语法格式：语法格式： ALTER TRIGGER trigger_name ON table | view 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_operator

21、column_bitmask .n Sql 语句语句 .n 21语法格式：语法格式：DROP TRIGGER trigger_name ,.n 【例【例13-5】除去】除去“T_表改变显示表改变显示”触发器。触发器。USE 教学管理教学管理GOIF EXISTS (SELECT name FROM sysobjects WHERE name =T_表改变显示表改变显示 AND type = TR) DROP TRIGGER T_表改变显示表改变显示GO2删除触发器删除触发器2213.4 13.4 使用触发器实现强制业务规则使用触发器实现强制业务规则13.4.1 INSERT13.4.1 INS

22、ERT触发器触发器 INSERTINSERT出发器当向表中添加记录时触发，为了维出发器当向表中添加记录时触发，为了维护数据完整性，当表中添加了新的记录后，应该对其关护数据完整性，当表中添加了新的记录后，应该对其关联表的数据进行调整，以实时反应数据的变化。例如当联表的数据进行调整，以实时反应数据的变化。例如当某个学生选定了某门课的开课计划后，应当更新选课人某个学生选定了某门课的开课计划后，应当更新选课人数。数。 需要用到需要用到insertedinserted表，因为做表，因为做INSERTINSERT操作时，在操作时，在insertedinserted表中存放的是要增加到该触发器作用的表中表中

23、存放的是要增加到该触发器作用的表中的新的元组，而的新的元组，而deleteddeleted表示此时为空。表示此时为空。23 13.4.2 UPDATE触发器触发器 当更新表中的元组时触发执行当更新表中的元组时触发执行UPDATE触发器。可以用触发器。可以用UPDATE (column)测试在指定的列上进行的测试在指定的列上进行的INSERT或或UPDATE操作，不能用于操作，不能用于 DELETE 操作。可以指定多列。因为在操作。可以指定多列。因为在ON子句子句中指定了表名，所以在中指定了表名，所以在IF UPDATE子句中的列名前不要包含表名。子句中的列名前不要包含表名。在在INSERT操作

24、中操作中IF UPDATE将返回将返回TRUE值，因为这些列插入值，因为这些列插入了显式值或隐性（了显式值或隐性（NULL）值。也可以用）值。也可以用COLUMNS_UPDATED()来测试是否更新了指定的列。来测试是否更新了指定的列。COLUMNS_UPDATED函数返回函数返回varbinary位模式，表示插入或位模式，表示插入或更新了表中的哪些列。更新了表中的哪些列。COLUMNS_UPDATED函数以从左到右的函数以从左到右的顺序返回位，最右边的位表示表中的第一列；向左的下一位表示顺序返回位，最右边的位表示表中的第一列；向左的下一位表示第二列，依此类推。第二列，依此类推。24 13.4

25、.3 DELETE 13.4.3 DELETE 触发器触发器 当删除表中数据时触发执行当删除表中数据时触发执行DELETEDELETE触发器，用它可以实现触发器，用它可以实现级联删除。此时，用到级联删除。此时，用到deleteddeleted表，该表中存放的是刚刚表，该表中存放的是刚刚被删除的那些元组，而被删除的那些元组，而insertedinserted表为空。表为空。2513.4.4 INSTEAD OF 触发器触发器 用用INSTEAD OF可以指定执行触发器而不是执行触发语句本可以指定执行触发器而不是执行触发语句本身，从而屏蔽原来的身，从而屏蔽原来的SQL语句，而转向执行触发器内部的语

26、句，而转向执行触发器内部的SQL语语句。对同一操作只能定义一个句。对同一操作只能定义一个INSTEAD OF触发器。触发器。 可以用可以用INSTEAD OF进行业务规则的判断，进而决定是否执进行业务规则的判断，进而决定是否执行触发行触发SQL语句。语句。2613.4.5 递归触发器递归触发器当在当在sp_dboption中启用中启用recursive triggers设置时，设置时，SQL Server还允许触发器的递归调用。还允许触发器的递归调用。递归触发器允许发生两种类型的递归：递归触发器允许发生两种类型的递归： p 间接递归间接递归p 直接递归直接递归 使用间接递归时，应用程序更新表使

27、用间接递归时，应用程序更新表T1，从而激发触发器，从而激发触发器TR1，该，该触发器更新表触发器更新表T2。在这种情况下，触发器。在这种情况下，触发器T2将激发并更新将激发并更新T1。使用直接递归时，应用程序更新表使用直接递归时，应用程序更新表T1，从而激发触发器，从而激发触发器TR1，该，该触发器更新表触发器更新表T1。由于表。由于表T1被更新，触发器被更新，触发器TR1再次激发，再次激发，依此类推。依此类推。2713.5 13.5 使用触发器的使用触发器的T-SQLT-SQL限制限制CREATE TRIGGER必须是批处理中的第一条语句，并且只能应必须是批处理中的第一条语句，并且只能应用到

28、一个表中。用到一个表中。触发器只能在当前的数据库中创建，不过触发器可以引用当前数触发器只能在当前的数据库中创建，不过触发器可以引用当前数据库的外部对象。据库的外部对象。如果指定触发器所有者名称以限定触发器，请以相同的方式限定如果指定触发器所有者名称以限定触发器，请以相同的方式限定表名。表名。在同一条在同一条CREATE TRIGGER语句中，可以为多种用户操作（如语句中，可以为多种用户操作（如INSERT和和UPDATE）定义相同的触发器操作。）定义相同的触发器操作。 2813.6 13.6 触发器应用实例分析触发器应用实例分析分析分析1 1：由于学生选课管理的实际情况，学生在期初或前一：由于

29、学生选课管理的实际情况，学生在期初或前一学期结束之前就进行选课，而成绩是在学期末考试后输入，学期结束之前就进行选课，而成绩是在学期末考试后输入，所以录入成绩实际上是对所以录入成绩实际上是对ENROLLENTENROLLENT表的数据的修改。故我表的数据的修改。故我们可以创建该表的修改触发器，实现学分的自动累计。由们可以创建该表的修改触发器，实现学分的自动累计。由于成绩修改于成绩修改UPDATEUPDATE语句可能涉及多个学生，故我们要在触语句可能涉及多个学生，故我们要在触发器中使用游标对每个学生进行判断修改。发器中使用游标对每个学生进行判断修改。29CREATE TRIGGER T_选课学分修

30、改选课学分修改ON 选课表选课表FOR UPDATEASBEGINIF (ROWCOUNT0) BEGIN DECLARE old_成绩成绩FLOAT, new_成绩成绩FLOAT DECLARE 学号学号_d CHAR(7),开课号开课号_d CHAR(6),学号学号_i CHAR(7),开课号开课号_i CHAR(6) DECLARE 学分学分INT DECLARE CUR_选课新信息选课新信息CURSOR FOR SELECT 学号学号,开课号开课号,成绩成绩 FROM inserted DECLARE CUR_选课旧信息选课旧信息CURSOR FOR SELECT 学号学号,开课号开课

31、号,成绩成绩 FROM deleted 【例【例13-1213-12】创建选课表的】创建选课表的UPDATEUPDATE触发器，实现学分的级联触发器，实现学分的级联修改。修改。30 OPEN CUR_选课新信息选课新信息 OPEN CUR_选课旧信息选课旧信息 FETCH NEXT FROM CUR_选课新信息选课新信息 INTO 学号学号_i, 开课号开课号_i,new_成绩成绩 FETCH NEXT FROM CUR_选课旧信息选课旧信息 INTO 学号学号_d, 开课号开课号_d,old_成绩成绩 SELECT 学分学分=学分学分 FROM 开课表开课表O,课程表课程表C WHERE O

32、.课号课号=C.课号课号AND 开课号开课号=开课号开课号_i WHILE fetch_status=0 BEGIN IF (old_成绩成绩is NULL) AND (new_成绩成绩=60) UPDATE 学生表学生表 SET 累计学分累计学分=累计学分累计学分+学分学分 WHERE 学号学号=学号学号_i IF (old_成绩成绩= 60) UPDATE 学生表学生表 SET 累计学分累计学分=累计学分累计学分+学分学分 WHERE 学号学号=学号学号_i 31IF (old_成绩成绩= 60) AND (new_成绩成绩0)BEGIN DECLARE 成绩成绩FLOAT DECLARE 学号学号CHAR(7),开课号开课号CHAR(6) DECLARE 学分学分INT DECLARE CUR_选课删除选课删除CURSOR FOR SELECT 学号学号,开课号开课号,成绩成绩 FROM deleted35OPEN CUR_选课删除选课删除FETCH NEXT FROM CUR_选课删除选课删除INTO 学号学号, 开课号开课号, 成绩成绩SELECT 学分学分=学分学分 FROM 开课表开课表O,课程表课程