触发器 基础与应用（二）

1. 触发器的概念 触发器是一种特殊类型的存储过程。当表中数据被修改时，SQL Server将自动执行触发器。使用触发器可以实施较为复杂的数据完整性约束。 在SQL Server中，数据库的表之间可以声明参照完整性约束，即主键(PRIMARYKEY)和外键(FOREIGNKEY)约束。在同一数据库中的简单参照完整性通常由主键和外键约束来实现。但是，由于声明参照完整性约束不能参照其他数据库中的对象，而对于数据库之间的参照完整性约束就只有通过触发器来实现。引用完整性约束与触发器，对数据操作构成了双重的有效性约束。 当对一个同时具有约束和触发器的表进行数据修改操作时，SQL Server将先进行约束检查，然后再执行触发器检查。如果这些语句的操作符合约束条件，系统将完成数据操作，然后再激活触发器；如果该语句未能通过约束检查，将不执行该数据操作语句，也就不可能激活触发器。如果某语句通过了约束检查而未能通过触发器检查，触发器也将取消该语句已经执行完毕的操作。 触发器的主要优点是： 任何一种对触发器表中数据进行修改的操作（包括操作人员录入数据或其他应用程序的修改）都能自动激活触发器，从而触发对这些操作进行的完整性检查。 (1)触发器能够实施的检查和操作比主键和外键约束、CHECK约束和规则对象等更为复杂。例如，CHECK约束只能根据一个逻辑表达式或同一个表中其他列值来检查指定列值的有效性，而使用触发器时则可以参照其他表中的列值。 (2)触发器建立在表一级，它与特定的数据修改事件相对应。而INSERT、UPDATE和DELETE三种操作都可能导致数据的修改，所以SQL Server中的触发器可分为 INSERT触发器、UPDATE触发器和DELETE触发器三种。而且SQL Server 2000版本中允许对同一数据对象修改事件、同一个数据操作定义多个触发器，这在SQLServer7.0以前的版本中是被禁止的。 触发器只能由数据库所有者创建。因为当为某一个表或表的列、行创建触发器时，表的访问方式及其对象之间的关系也随之改变了。也就是说，触发器的创建变动了数据库模式，所以创建触发器的权利应该保留给数据库的所有者，以防止普通用户无意间修改了系统格局。2. 触发器的创建 触发器(TRIGGER)由Transact-SQL中的CREATE TRIGGER语句创建，语句中应该包含所定义触发器基于的表、激活触发器的数据操作事件名称以及触发器所要执行的相应操作。CREATE TRIGGER语句不允许带参数，也不允许被直接调用，而只能由系统自动激活。CREATE TRIGGER定义触发器有两种格式。第一种格式定义触发器的触发事件为INSERT、UPDATE、DELETE等操作。其语法格式如下： CREATE TRIGGER[owner,]trigger_name On[owner,]table_name [WITH ENCRYPTION] { FOR{[INSERT][,UPDATE][,DELETE]} [NOT FORREPLICATION] AS sql_statements } 在上述语句中： ① trigger_name：为创建的触发器名称，它必须遵守SQL Server的命名规则，而且同一个数据库中不允许出现触发器名称相同的情况。 ② table_name：为该语句中定义的触发器所基于的表，也称为触发表。 ⑧ WITH ENCRYPTION选项：SQL Server将触发器的定义文本保存在系统分类表 syscomments中，选用此项要求SQL Server对该触发器定义文本进行加密存储，以防止第三方用户从syscomments表中读取该触发器定义文本的内容。 ④ NOT FOR REPLICATION选项：说明在复制过程中出现修改触发表数据时，触发器不被激活。 ⑤ FOR{[INSERT][,UPDATE][,DELETE]}：定义触发器的触发事件。当某一个触发器由表中的多个事件触发时，可以在INSERT、UPDATE、DELETE之间使用“，”符号作为间隔符来组合表示。 ⑥sql_statements：定义触发器在触发事件发生时(被激活时)所要执行的操作。 创建触发器的第二种定义格式中的触发事件仅限于INSERT和UPDATE。其语法格式为： CREATE TRIGGER[owner,]trigger_name On[owner,]table_name [WITH ENCRYPTION] { FOR{[INSERT][,UPDATE]} [NOT FOR REPLICATION] AS {IF UPDATE(column) [{AND|OR}UPDATE(column)] | IF(COLUMNS UPDATED(){bitwise_operator}updated_bitmask {comparison_operator column_bitmask} sql_statements } } 在第二种格式中，需要用IF子句进一步说明触发器的触发条件仅限于IF语句中指定的列值被修改。IF子句指定的被修改列有以下两种格式： IF UPDATE(column)[{AND|OR}UPDATE(column)] 和 IF(COLUMNS_UPDATED(){位运算符)updated_bitmask(L[较运算符 column_bitmask)其中： ①column：指明激活触发器的触发条件中其数据将被INSERT或UPDATE操作修改的列。 ②updated bitmask：定义了各位的位屏蔽值。 ⑧column bitmask：定义了各待检测列的位屏蔽值的值。各列的屏蔽位为该列在触发表中的序号。 ④COLUMNS_UPDATED()：用来检测指定列的列值是否被插入或修改。需要用位运算符、比较运算符和updated_bitmask(各位的位屏蔽值)、column__bitmask(各待检测列的位屏蔽值的值)等几个参数一起说明待检测列。假如一个触发表中有5个待检测列，则各列对应的屏蔽位为1～5，对应的位屏蔽值为20～24。例如，要在触发器中检测列1和列3的列值是否被修改的IF子句为：IF(COLUMNS UPDATED()&(1+4))<>0注意：在使用WITH ENCRYPTION选项时需要注意两点：一是原始触发器的文件丢失，将不能从syscomments表中重新保存加密文本；二是文本加密后，在数据库升级为新版本时不能修改，也不能重新存入新版本中。也就是说，如果触发器文本有可能需要修改，就不要随便将其加密。3. 触发器的使用、限制及嵌套 当触发器执行时，将生成两个特殊的临时表：inserted和deleted。它们与触发表的结构相同，用于测试触发条件，用户无法直接更改其中的内容。 SQL Server在执行INSERT语句时，将要插入触发表的新记录行同时插入inserted表中：执行DELETE语句时，将触发表中将要被删除的行放入deleted表中。在执行 UPDATE语句时，先从表中删除旧行，并将删除的行插入deleted表中。然后插入新行，并将新行插入inserted表中。　　提示：在使用触发器前，有时需要检查一下全局变量@@rowcount，以确定使用信息前的状况。因为大多数触发器都要影响到多行甚至多个表中的数据。向某一列插入NULL值或为列赋值时使用了DEFAULT关键字，都将激活触发器。在使用INSERT语句时如果没有为列提供列值，但向列中插入了隐含的NULL值或默认值时，也将激活触发器。提示：对某一个数据操作语句，就算影响到多行数据，同一个触发器也只能被激活一次。3.1 创建触发器1. 在“企业管理器”中创建。2. 在“查询分析器”中执行SQL语句创建。3.2 INSERT触发器 INSERT及UPDATE触发器经常用于检测触发器所监控表的列及其数据是否符合所定义的规则。它们可以在数据输入表之前，对其进行在定义引用完整性时无法完成的约束检验。下面以学生数据库student为例来介绍INSERT触发器的使用。该数据库包括三个表，分别是描述学生情况的“学生档案”表、描述学生成绩的“学生成绩”表和。描述分组情况的“分组情况”表。学生档案表样本s-id 学号 姓名 性别 班级 小组1 001 王小童 男 初二一班 12 002 张柳风 女 初二一班 13 003 紫云飞 女 初二一班 24 004 黄天龙 男 初二二班 1学生成绩表样本s_id 科目 成绩1 语文 671 数学 881 英语 982 语文 452 数学 892 英语 833 语文 943 数学 784 英语 81分组情况表班级 小组 人数初二一班 1 2初二一班 2 1初二二班 1 1为上面的“学生档案” 表创建一个INSERT触发器instrg，其作用是每新增一名学生而需向“学生档案”表中插入新行时，在“分组情况”表中将其所在小组的人数自动增加1。USE studentGOCREATE TRIGGER instrg ON [dbo].[学生档案]FOR INSERTASdeclare @班级 varchar(50), @小组 varchar(50), @人数 tinyintselect @班级=inserted.班级,@小组=inserted.小组 from insertedif exists (select 人数 from 分组情况 where @班级=分组情况.班级 and @小组=分组情况.小组)begin--bg1 select @人数=人数 from 分组情况 where @班级=分组情况.班级 and @小组=分组情况.小组 set @人数=@人数+1 update 分组情况 set 分组情况.人数=@人数 where @班级=分组情况.班级 and @小组=分组情况.小组end--bg1else begin--bg2 insert into 分组情况 (班级,小组,人数) values (@班级,@小组,1)end--b2 在触发器设计时应该尽可能地周密考虑，否则可能造成执行结果错误。在上述例子中，使用了触发器临时表inserted。当向“学生档案”表中插入新行时，触发器被激活，将自动在inserted表中检测新增行，并根据班级和小组号相应地将“分组情况”表中的人数值进行增加。测试SQL语句：insert into 学生档案 values (''001'',''王小童'',''男'',''初二一班'',''1'')insert into 学生档案 values (''001'',''张柳风'',''女'',''初二一班'',''1'')insert into 学生档案 values (''001'',''紫云飞'',''女'',''初二一班'',''2'')insert into 学生档案 values (''001'',''黄天龙'',''男'',''初二二班'',''1'')3.3 UPDATE触发器 对前述的“学生档案”情况数据库，如果班级的学生信息发生更改，需创建一个UPDATE 触发器将“分组情况”表进行相应改变。UPDATE触发器的定义语句为： CREATE TRIGGER updataStu ON [dbo].[学生档案] FOR UPDATEAS declare @stuGrade varchar(50),@stuGroup int,@stuCount int select @stuGrade=班级,@stuGroup=小组 from inserted if exists (select *from 分组 where 分组.班级=@stuGrade and 分组.小组=@stuGroup) begin--bg1 update 分组 set 分组.人数=分组.人数+1 where 分组. 班级=@stuGrade and 分组.小组=@stuGroup end --bg1 else begin--bg2 insert into 分组(班级,小组,人数) values(@stuGrade,@stuGroup,1) end --bg2 select @stuGrade=班级,@stuGroup=小组 from deleted select @stuCount=分组.人数 from 分组 where 分组.班级=@stuGrade and 分组.小组=@stuGroup if @stuCount>1 begin--bg3 update 分组 set 分组.人数=分组.人数-1 where 分组.班级=@stuGrade and 分组.小组=@stuGroup end--bg3 else begin--bg4 delete 分组 where 分组.班级=@stuGrade and 分组.小组=@stuGroup end--bg4用户从上述触发器的定义语句中，还可以体会到SQL Server为触发器所建立的两个临时表inserted和deleted的用法和优越性，以及使用Transact-SQL语言进行查询的细微之处。3.4 DELETE触发器DELETE触发器可用于防止删除某些数据(如作为外关键字使用的记录)，以保证数据的完整性。它还可以用于嵌套删除操作，即在删除父记录的同时级联删除子记录。在“学生档案”表中，如果将某学生转学，可以建立一个DELETE触发器实现在“分组情况”表中做相应修改，并将“学生成绩”表中相应的记录删除。触发器的定义语句如下：CREATE TRIGGER DelR ON [dbo].[学生档案]FOR DELETEASdelete 学生成绩 where 学生成绩.学号=(select deleted.学号 from deleted)3.5 Rollback触发器 Rollback触发器是一种特殊的触发器，其功能就是“取消”所有的触发器语句时SQL Server将停止执行触发器，同时也取消可能激活触发器的数据修改操作。 Rollback触发器的使用格式为：Rollback trigger[with raiserror error_number[message]]其中：①error_num ber：为出现的错误号。②message：为出现错误时的选择信息。⑧raiserror：向用户提供的详细、特殊的错误信息，可以包括错误文本、严重等级及状态信息以进一步说明错误的情况。另外raiserror还可以单独用于书写一般的错误语句。　　注意：在使用Rollback trigger语句时，只要没有使用raiserror选项，就必须给出错误号或选择信息，否则调用例程无法知道操作已被终止。Rollback触发器不能终止用于修改的批处理操作。因为修改数据库的代码会自动检测修改操作的返回值(通过检测全局变量@@error)，以确保修改操作成功完成。例一：部分回滚若要在触发器中进行部分回滚，必须使用 SAVE TRANSACTION 语句。提示: 事务保存点保存点提供了一种机制，用于回滚部分事务。可以使用 SAVE TRANSACTION savepoint_name 语句创建一个保存点，然后再执行 ROLLBACK TRANSACTION savepoint_name 语句回滚到该保存点，从而无须回滚到事务的开始。CREATE TRIGGER TestTrig ON [dbo].[学生档案] FOR UPDATEASupdate 分组情况 set 人数=10 where 小组=''1''SAVE TRANSACTION MyNameupdate 分组情况 set 人数=15 where 班级=''初二二班''--IF (@@error <> 0)IF (@@error = 0)BEGINROLLBACK TRANSACTION MyNameEND 提示: 使用 @@ERROR如果最后的 Transact-SQL 语句执行成功，则 @@ERROR 系统函数返回 0；如果此语句产生错误，则 @@ERROR 返回错误号。每一个 Transact-SQL 语句完成时，@@ERROR 的值都会改变。因为每个 Transact-SQL 语句执行完毕时，@@ERROR 都会得到一个新的值，@@ERROR 可用以下两种方法处理： 在 Transact-SQL 语句后，马上检测或使用 @@ERROR。在 Transact-SQL 语句完成后，马上把 @@ERROR 存储到一个整型变量中。此变量的值可供以后使用。测试SQL语句update student set 学号=''010'' where s_id=13.6 触发器的限制 SQL Server对触发器使用最主要的约束是DELETE、INSERT、UPDATE操作激活并执行了触发器时，可以回退。而一般的SQL语句操作不能回退。除此之外，SQL　Server还有如下一些限制条件： (1)在批（一组以GO语句结束的Transact SQL语句）中调用CREATE　TABLE语句时，该语句必须是批中的第一条语句。 (2)触发器所建立的触发表只能是基表或生成视图的表，而不能是视图。 (3)建议最好不要在触发器内使用SELECT语句和变量赋值语句来返回一组结果。因为要实现这样的结果需要用户使用程序代码来完成，才能保证上述语句操作的所有数据都是从该触发器定义的变量中读入的。如果确实需要在触发器内对变量进行赋值，应该在触发器定义的开始部分使用SET NOCOUNT语句以禁止SQL Server返回结果。 (4)使用SET选项设置连接选项将改变操作环境，但SET的设置只在触发器内有效。触发器执行之后，各连接选项将恢复为触发器激活前的状态。 (5)当大型二进制对象表列的数据类型为TEXT及IMAGE时，对其所作的数据处理不会激活触发器。 (6)另外，WRITETEXT和TRUNCATE语句对触发表的数据操作也不会激活触发器。 在触发器定义文本中使用以下Transact SQL语句时，SQL Server将拒绝编译和存储： (1)所有数据库及其对象的创建语句：CREATE DATABASE／TABLE／INDEX／ PROCEDURE／DEFAULT／RULE／TRIGGER／VIEW／SCHEMA以及由CREATE TABLE或SELECTINTO创建临时表的操作语句。 (2)所有ALTER语句：ALTER DATADBASE／TABLE／PROCEDURE／TRIGGER／VIEW。 (3)所有DROP语句：DROP DATABASE／TABLE／INDEX／PROCEDURE／DEFAULT／RULE／TRIGGER／VIEW。 (4)所有物理磁盘修改语句：DISKINIT／RESIZE。 (5)所有数据库装载语句：LOAD DATABASE／LOG。 (6)所有重装语句：RESTORE DATABASE／LOG。 (7)对象权限语句：GRANT及REVOKE语句。 (8)UPDATE STATISTICS语句。 (9)RECONFIGURE语句。 (10)DENY语句。3.7 触发器的嵌套 触发器的嵌套也称为触发器的递归调用，指的是一个触发器被激活而修改触发表中的内容时，激活了建立在该表上的另一个触发器：另一个触发器又类似地在修改其他触发表时激活了第三个触发器：如此一层层地传递下去。 在SQLServer7.0以上的版本中，允许嵌套最多为16层的触发器。用户也可以通过设置Sp_configure中的嵌套触发器选项禁止触发器嵌套功能。 在Sp_configure系统存储过程中禁止触发器嵌套功能的设置语句格式如下： Sp_configure[configuration_option,[configure_value))其中： ①configuration_option：为需要设置的服务器选项。SQL Server在查找该选项时使用的是LIKE操作符，所以在上述语句中不必输入全名也可以正确地查找到该选项。但在上述语句中，如果包含空格或其他格式的configuration_option参数，则需要用引号“”括起来。 ②configure_value：为选定的服务器选项设置的值。 在Sp_configure中设定触发器嵌套功能的选项为nestedTrigger，因而禁止触发器嵌套的语句可以书写如下(设置其值为。即表示取消触发器嵌套)： Sp_configure “nestedTrigger”，0 或 Sp_configure “nested”，0 以及 Sp_configure “trig”，0这几种格式设置的结果都是一样的。3.8 触发器信息显示 有两种方法可以显示触发器信息以说明触发器在触发表上的操作：使用SQL Enterprise，Manager(企业管理器，SQL-EM)或系统过程Sp help、Sp_depends和 Sp helptexto 1.使用SQL-EM 使用SQL Enterprise Manager显示触发器信息的操作步骤为： (1)首先，运行SQL Enterprise Managero (2)选择要显示的触发器所在的Server(服务器)。 (3)在服务器上选择工作表及其所需的数据库和表o. (4)在[Action]菜单中选择[All Tasks]、[Manage Triggers]命令。 完成上述步骤后，将出现一个Trigger Properties窗口，在窗口的Text文本框中将列 出所有已激活的触发器。 2.使用系统过程 系统过程Sp_help用来说明触发器是否存在，Sp_depends说明引用对象，SP_helptext 为实际文本或原代码。 1)使用Sp_help 使用系统过程sp_help可以列出数据库中全部对象的相关信息，包括触发器的创建者、创建的时间等。其语法格式为： Sp__help[object_name]其中，object_name为要显示的对象。如果此项缺省，SQL Server将列出系统分类表 sysobject中所用对象的信息。 2)使用Sp_depends 使用系统存储过程Sp_depends可以返回数据库对象如表、视图与存储过程之间的依赖关系。其语法格式为： Sp_depends object_name 3)使用Sp_helptext 使用过程Sp_helptext可以很容易地在系统分类表syscomments中访问全部用户所定义对象的存储文本，如规则、缺省值、视图、触发器以及存储过程。其语法格式为： Sp_helptext object_name 过程Sp_depends和Sp_helptext都不允许缺少对象名称选项。4. 触发器的加密、解密4.1 触发器的加密软件发布后我们希望知识产权能得到应有的保护，存储过程是脚本语言，与编译语言不同，脚本语言是文本文件，脚本语言是解释执行的，对脚本语言的保护一般采用加密方式。编译语言的可执行代码是二进制代码，理论上是不可逆的，而加密文件是可以完全解密的。触发器的加密很简单，只需要在表名字后加WITH ENCRYPTION即可。USE studentGOCREATE TRIGGER instrg1 ON [dbo].[学生档案]WITH ENCRYPTIONFOR INSERTASdeclare @班级 varchar(50), @小组 varchar(50), @人数 tinyintselect @班级=inserted.班级,@小组=inserted.小组 from insertedif exists (select 人数 from 分组情况 where @班级=分组情况.班级 and @小组=分组情况.小组)begin--bg1 select @人数=人数 from 分组情况 where @班级=分组情况.班级 and @小组=分组情况.小组 set @人数=@人数+1 update 分组情况 set 分组情况.人数=@人数 where @班级=分组情况.班级 and @小组=分组情况.小组end--bg1else begin--bg2 insert into 分组情况 (班级,小组,人数) values (@班级,@小组,1)end--b2当我们试图打开加密后的触发器时，系统显示： 4.2 触发器的解密前面提到加密过的触发器可以解密，网上可以找到很多解密工具，我们以SqlDecry2000为例来示范触发器的解密过程。5. 触发器编程的示例我们设计一个简单的员工管理系统，数据库名为manpower，其中有两个表，职工表employee和部门表department职工表employee e-id 职工号 姓名 性别 岗位 上级受雇日期 工资 部门号1 1002 王小童 男 秘书 1997-07-03 720.00 012 3039 张柳风 女 销售员 1998-02-04 650.00 033 3015 紫云飞 女 销售经理 1996-04-11 1200.00 034 2021 黄天龙 男 生产组长 1995-03-24 970.00 025 4017 张光耀 男 维修员 1997-11-23 810.00 04部门表departmentd-id 部门号 部门名称 人数 工资总额 部门经理1 01 总公司 32 3220.00 陶子乐2 02 生产部 56 4660.00 陈　路3 03 销售部 29 3150.00 紫云飞4 04 维修部 44 2580.00 宋　建5.1 INSERT触发器为上面的职工表创建一个INSERT触发器instrg，其作用是每新增一名职工而要向employee表中插入新行时，在department表中将其所在部门人数自动增加1，同时该部门的工资总额也需增加。CREATE TRIGGER instrg ON [dbo].[employee] FOR INSERTAS update department set 人数=人数+ (select count(*) from inserted group by inserted.部门号 having department.部门号=inserted.部门号) update department set 工资总额=工资总额+ (select sum(工资) from inserted group by inserted.部门号 having department.部门号=inserted.部门号)6. 触发器和事务在触发器中执行的动作实际上在背后是作为事务的一部分来执行的，过程如下：1. 隐含地发出一个BEGIN TRANSACTION语句给带触发器的表2. 发生插入、更新或删除操作3. 调用触发器，执行它的语句4. 触发器或者回滚事务，或者事务被隐含地提交