触发器详细介绍.doc

1、触发器1、触发器概述触发器是一种特殊类型的存储过程，不由用户直接调用。创建触发器时会对其进行定义，以便在对特定表或列作特定类型的数据修改时执行。CREATE PROCEDURE 或 CREATE TRIGGER 语句不能跨越批处理。即存储过程或触发器始终只能在一个批处理中创建并编译到一个执行计划中。用触发器还可以强制执行业务规则。Microsoft SQL Server 提供了两种主要机制来强制业务规则和数据完整性：约束和触发器。触发器是一种特殊类型的存储过程，它在指定的表中的数据发生变化时自动生效。唤醒调用触发器以响应 INSERT、UPDATE 或 DELETE 语句。触发器可以查询其它表

2、，并可以包含复杂的 Transact-SQL 语句。将触发器和触发它的语句作为可在触发器内回滚的单个事务对待。如果检测到严重错误（例如，磁盘空间不足） ，则整个事务即自动回滚。二、触发器的优点触发器可通过数据库中的相关表实现级联更改；不过，通过级联引用完整性约束可以更有效地执行这些更改触发器可以强制比用 CHECK 约束定义的约束更为复杂的约束。与 CHECK 约束不同的是，触发器可以引用其它表中的列。例如，触发器可以使用另一个表中的 SELECT 比较插入或更新的数据，以及执行其它操作，如修改数据或显示用户定义错误信息。触发器也可以评估数据修改前后的表状态，并根据其差异采取对策。一个表中的多

3、个同类触发器（INSERT、UPDATE 或 DELETE）允许采取多个不同的对策以响应同一个修改语句。 触发器是自动的：它们在对表的数据作了任何修改（比如手工输入或者应用程序采取的操作）之后立即被激活。 触发器可以通过数据库中的相关表进行层叠更改。例如，可以在 titles 表的 title_id 列上写入一个删除触发器，以使其它表中的各匹配行采取删除操作。该触发器用 title_id 列作为唯一键，在 titleauthor、sales 及 roysched 表中对各匹配行进行定位。 三、比较触发器与约束约束和触发器在特殊情况下各有优势。触发器的主要好处在于它们可以包含使用 Transac

4、t-SQL 代码的复杂处理逻辑。因此，触发器可以支持约束的所有功能；但它在所给出的功能上并不总是最好的方法。实体完整性总应在最低级别上通过索引进行强制，这些索引或是 PRIMARY KEY 和 UNIQUE 约束的一部分，或是在约束之外独立创建的。假设功能可以满足应用程序的功能需求，域完整性应通过 CHECK 约束进行强制，而引用完整性 (RI) 则应通过 FOREIGN KEY 约束进行强制。在约束所支持的功能无法满足应用程序的功能要求时，触发器就极为有用。例如： 除非 REFERENCES 子句定义了级联引用操作，否则 FOREIGN KEY 约束只能以与另一列中的值完全匹配的值来验证列值

5、。CHECK 约束只能根据逻辑表达式或同一表中的另一列来验证列值。如果应用程序要求根据另一个表中的列验证列值，则必须使用触发器。约束只能通过标准的系统错误信息传递错误信息。如果应用程序要求使用（或能从中获益）自定义信息和较为复杂的错误处理，则必须使用触发器。触发器可通过数据库中的相关表实现级联更改；不过，通过级联引用完整性约束可以更有效地执行这些更改。触发器可以禁止或回滚违反引用完整性的更改，从而取消所尝试的数据修改。当更改外键且新值与主键不匹配时，此类触发器就可能发生作用。例如，可以在 titleauthor.title_id 上创建一个插入触发器，使它在新值与 titles.title_i

6、d 中的某个值不匹配时回滚一个插入。不过，通常使用 FOREIGN KEY 来达到这个目的。如果触发器表上存在约束，则在 INSTEAD OF 触发器执行后但在 AFTER 触发器执行前检查这些约束。如果约束破坏，则回滚 INSTEAD OF 触发器操作并且不执行 AFTER 触发器。四、创建一个简单的触发器触发器是一种特殊的存储过程，类似于事件函数，SQL Server 允许为 INSERT、UPDATE、DELETE 创建触发器，即当在表中插入、更新、删除记录时，触发一个或一系列 T-SQL 语句。触发器可以在查询分析器里创建，也可以在表名上点右键-“所有任务”-“ 管理触发器”来创建，不

7、过都是要写 T-SQL 语句的，只是在查询分析器里要先确定当前操作的数据库。创建触发器用 CREATE TRIGGERCREATE TRIGGER 触发器名称ON 表名FOR INSERT、UPDATE 或 DELETEAST-SQL 语句注意：触发器名称是不加引号的。如下是联机丛书上的一个示例，当在 titles 表上更改记录时，发送邮件通知 MaryM。CREATE TRIGGER reminderON titlesFOR INSERT, UPDATE, DELETEASEXEC master.xp_sendmail MaryM,Dont forget to print a report

8、for the distributors.五、删除触发器用查询分析器删除在查询分析器中使用 drop trigger 触发器名称 来删除触发器。也可以同时删除多个触发器：drop trigger 触发器名称,触发器名称.注意：触发器名称是不加引号的。在删除触发器之前可以先看一下触发器是否存在：if Exists(select name from sysobjects where name=触发器名称 and xtype=TR)用企业管理器删除在企业管理器中，在表上点右键-“所有任务”-“管理触发器”，选中所要删除的触发器，然后点击“删除” 。六、重命名触发器用查询分析器重命名exec sp_r

9、ename 原名称, 新名称sp_rename 是 SQL Server 自带的一个存储过程，用于更改当前数据库中用户创建的对象的名称，如表名、列表、索引名等。用企业管理器重命名在表上点右键-“所有任务”-“管理触发器”，选中所要重命名的触发器，修改触发器语句中的触发器名称，点击“确定 ”。四、more.INSTEAD OF执行触发器语句，但不执行触发触发器的 SQL 语句，比如试图删除一条记录时，将执行触发器指定的语句，此时不再执行 delete 语句。例：create trigger fon tblinstead of deleteasinsert into Logs.IF UPDATE(

10、列名)检查是否更新了某一列，用于 insert 或 update，不能用于 delete。例：create trigger fon tblfor updateasif update(status) or update(title)sql_statement -更新了 status 或 title 列inserted、deleted这是两个虚拟表，inserted 保存的是 insert 或 update 之后所影响的记录形成的表，deleted 保存的是 delete 或 update 之前所影响的记录形成的表。例：create trigger tbl_deleteon tblfor dele

11、teasdeclare title varchar(200)select title=title from deletedinsert into Logs(logContent) values(删除了 title 为： + title + 的记录)说明：如果向 inserted 或 deleted 虚拟表中取字段类型为 text、image 的字段值时，所取得的值将会是 null。七、查看数据库中所有的触发器在查询分析器中运行：use 数据库名goselect*fromsysobjectswherextype=TRsysobjects 保存着数据库的对象，其中 xtype 为 TR 的记录即为

12、触发器对象。在 name 一列，我们可以看到触发器名称。八、sp_helptext 查看触发器内容用查询分析器查看use 数据库名goexecsp_helptext触发器名称将会以表的样式显示触发器内容。除了触发器外，sp_helptext 还可以显示 规则、默认值、未加密的存储过程、用户定义函数、视图的文本用企业管理器查看在表上点右键-“所有任务”-“管理触发器”，选择所要查看的触发器存储过程九、sp_helptrigger 用于查看触发器的属性sp_helptrigger 有两个参数：第一个参数为表名；第二个为触发器类型，为 char(6) 类型，可以是 INSERT、UPDATE、DEL

13、ETE，如果省略则显示指定表中所有类型触发器的属性。例：use 数据库名goexecsp_helptriggertbl十、递归、嵌套触发器递归分两种，间接递归和直接递归。我们举例解释如下，假如有表1、表2名称分别为 T1、T2 ，在 T1、 T2 上分别有触发器 G1、G2。 间接递归：对 T1 操作从而触发 G1，G1 对 T2 操作从而触发 G2，G2 对 T1 操作从而再次触发 G1. 直接递归：对 T1 操作从而触发 G1，G1 对 T1 操作从而再次触发 G1.1、嵌套触发器类似于间接递归，间接递归必然要形成一个环，而嵌套触发器不一定要形成一个环，它可以 T1-T2-T3.这样一直触

14、发下去，最多允许嵌套 32 层。2、设置直接递归默认情况下是禁止直接递归的，要设置为允许有两种方法： T-SQL：exec sp_dboption dbName, recursive triggers, true EM：数据库上点右键- 属性- 选项。3、设置间接递归、嵌套默认情况下是允许间接递归、嵌套的，要设置为禁止有两种方法： T-SQL：exec sp_configure nested triggers, 0 -第二个参数为 1 则为允许 EM：注册上点右键- 属性-服务器设置。 十一、触发器回滚我们看到许多注册系统在注册后都不能更改用户名，但这多半是由应用程序决定的， 如果直接打开数据

15、库表进行更改，同样可以更改其用户名，在触发器中利用回滚就可以巧妙地实现无法更改用户名。use 数据库名gocreate trigger tron 表名for updateasif update(userName)rollback tran关键在最后两句，其解释为：如果更新了 userName 列，就回滚事务。十二、禁用、启用触发器禁用：alter table 表名 disable trigger 触发器名称启用：alter table 表名 enable trigger 触发器名称如果有多个触发器，则各个触发器名称之间用英文逗号隔开。如果把“触发器名称 ”换成“ALL”，则表示禁用或启用该表的

16、全部触发器。十三、触发器限制CREATE TRIGGER 必须是批处理中的第一条语句，并且只能应用到一个表中。触发器只能在当前的数据库中创建，不过触发器可以引用当前数据库的外部对象。如果指定触发器所有者名称以限定触发器，请以相同的方式限定表名。在同一条 CREATE TRIGGER 语句中，可以为多种用户操作（如 INSERT 和 UPDATE）定义相同的触发器操作。如果一个表的外键在 DELETE/UPDATE 操作上定义了级联，则不能在该表上定义 INSTEAD OF DELETE/UPDATE 触发器。在触发器内可以指定任意的 SET 语句。所选择的 SET 选项在触发器执行期间有效，并

17、在触发器执行完后恢复到以前的设置。与使用存储过程一样，当触发器激发时，将向调用应用程序返回结果。若要避免由于触发器激发而向应用程序返回结果，请不要包含返回结果的 SELECT 语句，也不要包含在触发器中进行变量赋值的语句。包含向用户返回结果的 SELECT 语句或进行变量赋值的语句的触发器需要特殊处理；这些返回的结果必须写入允许修改触发器表的每个应用程序中。如果必须在触发器中进行变量赋值，则应该在触发器的开头使用 SET NOCOUNT 语句以避免返回任何结果集。DELETE 触发器不能捕获 TRUNCATE TABLE 语句。尽管 TRUNCATE TABLE 语句实际上是没有 WHERE

18、子句的 DELETE（它删除所有行） ，但它是无日志记录的，因而不能执行触发器。因为 TRUNCATE TABLE 语句的权限默认授予表所有者且不可转让，所以只有表所有者才需要考虑无意中用 TRUNCATE TABLE 语句规避 DELETE 触发器的问题。无论有日志记录还是无日志记录，WRITETEXT 语句都不激活触发器。触发器中不允许以下 Transact-SQL 语句：ALTER DATABASE CREATE DATABASE DISK INITDISK RESIZE DROP DATABASE LOAD DATABASELOAD LOG RECONFIGURE RESTORE DA

19、TABASERESTORE LOG说明 由于 SQL Server 不支持系统表中的用户定义触发器，因此建议不要在系统表中创建用户定义触发器。十四、多个触发器SQL Server 允许为每个数据修改事件（DELETE、INSERT 或 UPDATE）创建多个触发器。例如，如果对已有 UPDATE 触发器的表执行 CREATE TRIGGER FOR UPDATE，则将创建另一个更新触发器。在早期版本中，在每个表上，每个数据修改事件（INSERT、UPDATE 或 DELETE）只允许有一个触发器。说明 如果触发器名称不同，则 CREATE TRIGGER（兼容级别为 70）的默认行为是在现有的

20、触发器中添加其它触发器。如果触发器名称相同，则 SQL Server 返回一条错误信息。但是，如果兼容级别等于或小于 65，则使用 CREATE TRIGGER 语句创建的新触发器将替换同一类型的任何现有触发器，即使触发器名称不同。有关更多信息，请参见 sp_dbcmptlevel。十五、递归触发器当在 sp_dboption 中启用 recursive triggers 设置时，SQL Server 还允许触发器的递归调用。递归触发器允许发生两种类型的递归：* 间接递归* 直接递归使用间接递归时，应用程序更新表 T1，从而激发触发器 TR1，该触发器更新表 T2。在这种情况下，触发器 T2

21、将激发并更新 T1。使用直接递归时，应用程序更新表 T1，从而激发触发器 TR1，该触发器更新表 T1。由于表 T1 被更新，触发器 TR1 再次激发，依此类推。下例既使用了间接触发器递归，又使用了直接触发器递归。假定在表 T1 中定义了两个更新触发器 TR1 和 TR2。触发器 TR1 递归地更新表 T1。UPDATE 语句使 TR1 和 TR2 各执行一次。而 TR1 的执行将触发 TR1（递归）和 TR2 的执行。给定触发器的 inserted 和 deleted 表只包含与唤醒调用触发器的 UPDATE 语句相对应的行。说明 只有启用 sp_dboption 的 recursive t

22、riggers 设置，才会发生上述行为。对于为给定事件定义的多个触发器，并没有确定的执行顺序。每个触发器都应是自包含的。禁用 recursive triggers 设置只能禁止直接递归。若要也禁用间接递归，请使用 sp_configure 将 nested triggers 服务器选项设置为 0。如果任一触发器执行了 ROLLBACK TRANSACTION 语句，则无论嵌套级是多少，都不会进一步执行其它触发器。十六、嵌套触发器触发器最多可以嵌套 32 层。如果一个触发器更改了包含另一个触发器的表，则第二个触发器将激活，然后该触发器可以再调用第三个触发器，依此类推。如果链中任意一个触发器引发了

23、无限循环，则会超出嵌套级限制，从而导致取消触发器。若要禁用嵌套触发器，请用 sp_configure 将 nested triggers 选项设置为 0（关闭） 。默认配置允许嵌套触发器。如果嵌套触发器是关闭的，则也将禁用递归触发器，与 sp_dboption 的 recursive triggers 设置无关。延迟名称解析SQL Server 允许 Transact-SQL 存储过程、触发器和批处理引用编译时不存在的表。这种能力称为延迟名称解析。但是，如果 Transact-SQL 存储过程、触发器或批处理引用在存储过程或触发器中定义的表，则只有当兼容级别设置（通过执行 sp_dbcmptl

24、evel 设置）等于 65 时，才会在创建时发出警告。如果使用批处理，则在编译时发出警告。如果引用的表不存在，将在运行时返回错误信息。有关更多信息，请参见延迟名称解析和编译。十七、权限CREATE TRIGGER 权限默认授予定义触发器的表所有者、sysadmin 固定服务器角色成员以及 db_owner 和 db_ddladmin 固定数据库角色成员，并且不可转让。若要检索表或视图中的数据，用户必须在表或视图中拥有 SELECT 语句权限。若要更新表或视图的内容，用户必须在表或视图中拥有 INSERT、DELETE 和 UPDATE 语句权限。如果视图中存在 INSTEAD OF 触发器，用户必须在该视图中有 INSERT、DELETE 和 UPDATE 特权，以对该视图发出 INSERT、DELETE 和 UPDATE 语句，而不管实际上是否在视图上执行了这样的操作。