1、- 1 -Sql Server 数据库触发器实例讲解文章出处定义： 何为触发器？在 SQL Server 里面也就是对某一个表的一定的操作，触发某种条件，从而执行的一段程序。触发器是一个特殊的存储过程。常见的触发器有三种：分别应用于 Insert , Update , Delete 事件。(SQL Server 2000 定义了新的触发器，这里不提）我为什么要使用触发器？比如，这么两个表：Create Table Student( -学生表StudentID int primary key, -学号 )Create Table BorrowRecord( -学生借书记录表BorrowRecor
2、d int identity(1,1), -流水号 StudentID int , -学号BorrowDate datetime, -借出时间ReturnDAte Datetime, -归还时间 . )用到的功能有:1.如果我更改了学生的学号,我希望他的借书记录仍然与这个学生相关(也就是同时更改借书记录表的学号);2.如果该学生已经毕业，我希望删除他的学号的同时，也删除它的借书记录。等等。这时候可以用到触发器。对于 1，创建一个 Update 触发器：Create Trigger truStudentOn Student -在 Student 表中创建触发器for Update -为什么事件触
3、发As -事件触发后所要做的事情if Update(StudentID) beginUpdate BorrowRecord Set StudentID=i.StudentIDFrom BorrowRecord br , Deleted d ,Inserted i -Deleted 和Inserted 临时表Where br.StudentID=d.StudentIDend 理解触发器里面的两个临时的表：Deleted , Inserted 。注意 Deleted 与 Inserted分别表示触发事件的表“旧的一条记录”和“新的一条记录” 。一个数据库系统中有两个虚拟表用于存储在表中记录改动的信
4、息，分别是：虚拟表 Inserted 虚拟表 Deleted在表记录新增时 存放新增的记录 不存储记录修改时 存放用来更新的新记录 存放更新前的记录删除时 不存储记录 存放被删除的记录一个 Update 的过程可以看作为：生成新的记录到 Inserted 表，复制旧的记录到Deleted 表，然后删除 Student 记录并写入新纪录。对于 2，创建一个 Delete 触发器Create trigger trdStudentOn Student- 2 -for DeleteAsDelete BorrowRecord From BorrowRecord br , Delted dWhere br
5、.StudentID=d.StudentID从这两个例子我们可以看到了触发器的关键：A.2 个临时的表；B.触发机制。这里我们只讲解最简单的触发器。复杂的容后说明。事实上，我不鼓励使用触发器。触发器的初始设计思想，已经被“级联”所替代SQLSERVER 触发器触发器是数据库应用中的重用工具，它的应用很广泛。这几天写一个化学数据统计方面的软件，需要根据采样，自动计算方差，在这里，我使用了触发器。 可以定义一个无论何时用 INSERT 语句向表中插入数据时都会执行的触发器。 当触发 INSERT 触发器时，新的数据行就会被插入到触发器表和 inserted 表中。inserted 表是一个逻辑表，
6、它包含了已经插入的数据行的一个副本。inserted 表包含了INSERT 语句中已记录的插入动作。inserted 表还允许引用由初始化 INSERT 语句而产生的日志数据。触发器通过检查 inserted 表来确定是否执行触发器动作或如何执行它。inserted 表中的行总是触发器表中一行或多行的副本。 日志记录了所有修改数据的动作(INSERT、UPDATE 和 DELETE 语句)，但在事务日志中的信息是不可读的。然而，inserted 表允许你引用由 INSERT 语句引起的日志变化，这样就可以将插入数据与发生的变化进行比较，来验证它们或采取进一步的动作。也可以直接引用插入的数据，而
7、不必将它们存储到变量中。 示例 在本例中，将创建一个触发器。无论何时订购产品（无论何时向 Order Details 表中插入一条记录） ，这个触发器都将更新 Products 表中的一列(UnitsInStock)。用原来的值减去订购的数量值即为新值。 USE Northwind CREATE TRIGGER OrdDet_Insert ON Order Details FOR INSERT AS UPDATE P SET UnitsInStock = P.UnitsInStock I.Quantity FROM Products AS P INNER JOIN Inserted AS I
8、ON P.ProductID = I.ProductID DELETE 触发器的工作过程 当触发 DELETE 触发器后，从受影响的表中删除的行将被放置到一个特殊的 deleted表中。deleted 表是一个逻辑表，它保留已被删除数据行的一个副本。deleted 表还允许引用由初始化 DELETE 语句产生的日志数据。 使用 DELETE 触发器时，需要考虑以下的事项和原则： 当某行被添加到 deleted 表中时，它就不再存在于数据库表中；因此，deleted 表和数据库表没有相同的行。 创建 deleted 表时，空间是从内存中分配的。deleted 表总是被存储在高速缓存中。为 DEL
9、ETE 动作定义的触发器并不执行 TRUNCATE TABLE 语句，原因在于日志不记录- 3 -TRUNCATE TABLE 语句。 示例 在本例中，将创建一个触发器，无论何时删除一个产品类别（即从 Categories 表中删除一条记录） ，该触发器都会更新 Products 表中的 Discontinued 列。所有受影响的产品都标记为 1，标示不再使用这些产品了。 USE Northwind CREATE TRIGGER Category_Delete ON Categories FOR DELETE AS UPDATE P SET Discontinued = 1 FROM Prod
10、ucts AS P INNER JOIN deleted AS d ON P.CategoryID = d.CategoryID UPDATE 触发器的工作过程 可将 UPDATE 语句看成两步操作：即捕获数据前像(before image)的 DELETE 语句，和捕获数据后像(after image)的 INSERT 语句。当在定义有触发器的表上执行 UPDATE 语句时，原始行（前像）被移入到 deleted 表，更新行（后像）被移入到 inserted 表。 触发器检查 deleted 表和 inserted 表以及被更新的表，来确定是否更新了多行以及如何执行触发器动作。 可以使用 I
11、F UPDATE 语句定义一个监视指定列的数据更新的触发器。这样，就可以让触发器容易的隔离出特定列的活动。当它检测到指定列已经更新时，触发器就会进一步执行适当的动作，例如发出错误信息指出该列不能更新，或者根据新的更新的列值执行一系列的动作语句。 语法 IF UPDATE () 例 1 本例阻止用户修改 Employees 表中的 EmployeeID 列。 USE Northwind GO CREATE TRIGGER Employee_Update ON Employees FOR UPDATE AS IF UPDATE (EmployeeID) BEGIN RAISERROR (Trans
12、action cannot be processed. * Employee ID number cannot be modified., 10, 1) ROLLBACK TRANSACTION END INSTEAD OF 触发器的工作过程 可以在表或视图上指定 INSTEAD OF 触发器。执行这种触发器就能够替代原始的触发动作。INSTEAD OF 触发器扩展了视图更新的类型。对于每一种触发动作(INSERT、UPDATE或 DELETE)，每一个表或视图只能有一个 INSTEAD OF 触发器。 INSTEAD OF 触发器被用于更新那些没有办法通过正常方式更新的视图。例如，通常不-
13、4 -能在一个基于连接的视图上进行 DELETE 操作。然而，可以编写一个 INSTEAD OF DELETE 触发器来实现删除。上述触发器可以访问那些如果视图是一个真正的表时已经被删除的数据行。将被删除的行存储在一个名为 deleted 的工作表中，就像 AFTER 触发器一样。相似地，在 UPDATE INSTEAD OF 触发器或者 INSERT INSTEAD OF 触发器中，你可以访问 inserted 表中的新行。 不能在带有 WITH CHECK OPTION 定义的视图中创建 INSTEAD OF 触发器。示例 在本例中，创建了一个德国客户表和一个墨西哥客户表。放置在视图上的
14、INSTEAD OF触发器将把更新操作重新定向到适当的基表上。这时发生的插入是对 CustomersGer 表的插入而不是对视图的插入。 创建两个包含客户数据的表： SELECT * INTO CustomersGer FROM Customers WHERE Customers.Country = Germany SELECT * INTO CustomersMex FROM Customers WHERE Customers.Country = Mexico GO 在该数据上创建视图： CREATE VIEW CustomersView AS SELECT * FROM Customers
15、Ger UNION SELECT * FROM CustomersMex GO 创建一个在上述视图上的 INSTEAD OF 触发器： CREATE TRIGGER Customers_Update2 ON CustomersView INSTEAD OF UPDATE AS DECLARE Country nvarchar(15) SET Country = (SELECT Country FROM Inserted) IF Country = Germany BEGIN UPDATE CustomersGer SET CustomersGer.Phone = Inserted.Phone
16、FROM CustomersGer JOIN Inserted ON CustomersGer.CustomerID = Inserted.CustomerID END ELSE IF Country = Mexico BEGIN UPDATE CustomersMex SET CustomersMex.Phone = Inserted.Phone FROM CustomersMex JOIN Inserted ON CustomersMex.CustomerID = Inserted.CustomerID END 通过更新视图，测试触发器： UPDATE CustomersView SET
17、Phone = 030-007xxxx WHERE CustomerID = ALFKI - 5 -SELECT CustomerID, Phone FROM CustomersView WHERE CustomerID = ALFKI SELECT CustomerID, Phone FROM CustomersGer WHERE CustomerID = ALFKI 那么具体的讲，对于多列数据，如何计算方差呢？： CREATE TRIGGER calT1T2T3 ON dbo.DCLB FOR INSERT,UPDATE AS update P SET /*/* 计算方差的触发器 */ P
18、.T1=(I.P1+I.P2+I.P3+I.P4+I.P5+I.P6), P.T2=(I.Y1+I.Y2+I.Y3+I.Y4+I.Y5+I.Y6 ), P.T3=SQRT(P.T1*P.T1+P.T2*P.T2) FROM DCLB AS P INNER JOIN Inserted AS I ON P.SID = I.SID 触发器的使用很方便，而且也很简单，重要的是理解 inserted 过程。可将 UPDATE 语句看成两步操作：即捕获数据前像(before image)的 DELETE 语句，和捕获数据后像(after image)的 INSERT 语句。当在定义有触发器的表上执行 UP
19、DATE 语句时，原始行（前像）被移入到 deleted 表，更新行（后像）被移入到 inserted 表。触发器检查 deleted 表和inserted 表以及被更新的表，来确定是否更新了多行以及如何执行触发器动作。初识 SQLSERVER 触发器(一)触发器是一种特殊的存储过程，类似于其它编程语言中的事件函数，SQL Server? 允许为 INSERT、UPDATE、DELETE 创建触发器，当在表（视图）中插入、更新、删除记录时，触发一个或一系列 T-SQL 语句。 本专题的目的并非是向您详尽地介绍触发器，而是系统地向您介绍触发器的常用知识与相关应用，并与您一起探讨应用经验，如果您需
20、要了解触发器不常用的知识点，请参见 SQL Server? 联机丛书（安装 SQL Server? 时默认是安装的） 。 a.基础创建一个简单的触发器 触发器是一种特殊的存储过程，类似于事件函数，SQL Server? 允许为 INSERT、UPDATE、DELETE 创建触发器，即当在表中插入、更新、删除记录时，触发一个或一系列 T-SQL 语句。 触发器可以在查询分析器里创建，也可以在表名上点右键-“所有任务”-“管理触发器”来创建，不过都是要写 T-SQL 语句的，只是在查询分析器里要先确定当前操作的数据库。创建触发器用 CREATE TRIGGERCREATE TRIGGER 触发器名
21、称ON 表名FOR INSERT、UPDATE 或 DELETEAST-SQL 语句注意：触发器名称是不加引号的。- 6 -如下是联机丛书上的一个示例，当在 titles 表上更改记录时，发送邮件通知 MaryM。CREATE TRIGGER reminderON titlesFOR INSERT, UPDATE, DELETE ASEXEC master.xp_sendmail MaryM, Dont forget to print a report for the distributors.b.基础-删除触发器用查询分析器删除在查询分析器中使用 drop trigger 触发器名称 来删除
22、触发器。也可以同时删除多个触发器：drop trigger 触发器名称,触发器名称.注意：触发器名称是不加引号的。在删除触发器之前可以先看一下触发器是否存在：if Exists(select name from sysobjects where name=触发器名称 and xtype=TR)用企业管理器删除在企业管理器中，在表上点右键-“所有任务”-“管理触发器” ，选中所要删除的触发器，然后点击“删除” 。c.基础-重命名触发器用查询分析器重命名exec sp_rename 原名称, 新名称sp_rename 是 SQL Server? 自带的一个存储过程，用于更改当前数据库中用户创建的对
23、象的名称，如表名、列表、索引名等。 用企业管理器重命名在表上点右键-“所有任务”-“管理触发器” ，选中所要重命名的触发器，修改触发器语句中的触发器名称，点击“确定” 。d.复杂一点的触发器INSTEAD OF执行触发器语句，但不执行触发触发器的 SQL 语句，比如试图删除一条记录时，将执行触发器指定的语句，此时不再执行 delete 语句。例： create trigger fon tblinstead of deleteasinsert into LogsIF UPDATE(列名)检查是否更新了某一列，用于 insert 或 update，不能用于 delete。例：create trig
24、ger fon tblfor updateasif update(status) or update(title)sql_statement -更新了 status 或 title 列inserted、deleted这是两个虚拟表，inserted 保存的是 insert 或 update 之后所影响的记录形成的表，- 7 -deleted 保存的是 delete 或 update 之前所影响的记录形成的表。例：create trigger tbl_deleteon tblfor deleteasdeclare title varchar(200)select title=title from
25、 deletedinsert into Logs(logContent) values(删除了 title 为： + title + 的记录)说明：如果向 inserted 或 deleted 虚拟表中取字段类型为 text、image 的字段值时，所取得的值将会是 null。sql server:触发器 收藏 触发器是一种特殊的存储过程，类似于其它编程语言中的事件函数，SQL Server 允许为 INSERT、UPDATE、DELETE 创建触发器，当在表（视图）中插入、更新、删除记录时，触发一个或一系列 T-SQL 语句。 本专题的目的并非是向您详尽地介绍触发器，而是系统地向您介绍触发器
26、的常用知识与相关应用，并与您一起探讨应用经验，如果您需要了解触发器不常用的知识点，请参见 SQL Server 联机丛书（安装 SQL Server 时默认是安装的） 。 在视图上创建普通触发器可能会出现“对象无效”的错误，实际上，我们不能在视图上创建 FOR 触发器，而应该创建 INSTEAD OF 触发器。在 SQL Server 联机丛书中，是没有说触发器不能在视图上创建的， 并且在语法解释中表明：在 CREATE TRIGGER 的 ON 之后可以是视图。 然而，事实似乎并不是如此，很多专家也说触发器不能在视图上创建。 我也专门作了测试，的确如此，不管是普通视图还是索引视图，都无法在上
27、面创建触发器. 但是无可厚非的是：当在临时表或系统表上创建触发器时会遭到拒绝。FOR CREATE TRIGGER 语句FOR 关键字之后可以跟 INSERT、UPDATE、DELETE 中的一个或多个，也就是说在其它情况下是不会触发触发器的， 包括 SELECT、TRUNCATE、WRITETEXT、UPDATETEXT。TRUNCATE TABLE 和不带 WHERE 的 DELETE 功能是一样的，都是删除表中的所有数据，不过 TRUNCATE TABLE 速度更快，占用的日志更少，这是因为 TRUNCATE TABLE 直接释放数据页并且在事务日志中也只记录数据页的释放，而 DELET
28、E 是一行一行地删除，在事务日志中要记录每一条记录的删除。 那么可不可以用 TRUNCATE TABLE 代替不带 WHERE 的 DELETE 呢？在以下情况是不行的： 1、要保留标识的情况下不能用 TRUNCATE TABLE，因为 TRUNCATE TABLE 会重置标识。 2、需要使用触发器的情况下不能使用 TRUNCATE TABLE ，它不会激发触发器。 3、对于由 FOREIGN KEY 约束引用的表（即主键所在的表，不是外键所在的表）不能使用 TRUNCATE TABLE。 4、对于参与了索引视图的表不能使用 TRUNCATE TABLE ，注意指索引视图，并非普通视图。触发器
29、内部语句出错时，前面对数据更改操作将会无效。 举个例子，在表中插入数据时触发触发器，而触发器内部此时发生了运行时错误，那么将返回一个错误值，并且拒绝刚才的数据插入。 NextPage一个有趣的触发器应用 触发器回滚我们看到许多注册系统在注册后都不能更改用户名，但这多半是由应用程序决定的， 如果- 8 -直接打开数据库表进行更改，同样可以更改其用户名，在触发器中利用回滚就可以巧妙地实现无法更改用户名。 use 数据库名 go create trigger tr on 表名 for update as if update(userName) rollback tran 关键在最后两句，其解释为：如
30、果更新了 userName 列，就回滚事务。 不能在触发器中使用的语句 触发器中可以使用大多数 T-SQL 语句，但如下一些语句是不能在触发器中使用的。 CREATE 语句，如：CREATE DATABASE、CREATE TABLE、CREATE INDEX 等。 ALTER 语句，如：ALTER DATABASE、ALTER TABLE、ALTER INDEX 等。 DROP 语句，如：DROP DATABASE、DROP TABLE、DROP INDEX 等。 DISK 语句，如：DISK INIT、DISK RESIZE。 LOAD 语句，如：LOAD DATABASE、LOAD LO
31、G。 RESTORE 语句，如：RESTORE DATABASE、RESTORE LOG。 RECONFIGURE 说明：有人说不能用 TRUNCATE TABLE 语句，其实是可以的。 NextPage递归、嵌套触发器 递归触发器 递归分两种，间接递归和直接递归。我们举例解释如下，假如有表 1、表 2 名称分别为 T1、T2，在 T1、T2 上分别有触发器 G1、G2。 间接递归：对 T1 操作从而触发 G1，G1 对 T2 操作从而触发 G2，G2 对 T1 操作从而再次触发 G1. 直接递归：对 T1 操作从而触发 G1，G1 对 T1 操作从而再次触发 G1. 嵌套触发器 类似于间接递
32、归，间接递归必然要形成一个环，而嵌套触发器不一定要形成一个环，它可以 T1-T2-T3.这样一直触发下去，最多允许嵌套 32 层。 - 设置直接递归 默认情况下是禁止直接递归的，要设置为允许有两种方法： T-SQL：exec sp_dboption dbName, recursive triggers, true EM：数据库上点右键-属性-选项。 设置间接递归、嵌套 默认情况下是允许间接递归、嵌套的，要设置为禁止有两种方法： T-SQL：exec sp_configure nested triggers, 0 -第二个参数为 1 则为允许 EM：注册上点右键-属性-服务器设置。 递归触发器
33、递归分两种，间接递归和直接递归。我们举例解释如下，假如有表 1、表 2 名称分别为 T1、T2，在 T1、T2 上分别有触发器 G1、G2。 间接递归：对 T1 操作从而触发 - 9 -G1，G1 对 T2 操作从而触发 G2，G2 对 T1 操作从而再次触发 G1. 直接递归：对 T1 操作从而触发 G1，G1 对 T1 操作从而再次触发 G1. 嵌套触发器 类似于间接递归，间接递归必然要形成一个环，而嵌套触发器不一定要形成一个环，它可以 T1-T2-T3.这样一直触发下去，最多允许嵌套 32 层。 - 设置直接递归 默认情况下是禁止直接递归的，要设置为允许有两种方法： T-SQL：exec
34、 sp_dboption dbName, recursive triggers, true EM：数据库上点右键-属性-选项。 设置间接递归、嵌套 默认情况下是允许间接递归、嵌套的，要设置为禁止有两种方法： T-SQL：exec sp_configure nested triggers, 0 -第二个参数为 1 则为允许 EM：注册上点右键-属性-服务器设置。 NextPage多个触发器 触发器的名称不同，触发事件相同（INSERT、UPDATE、DELETE） ，我们称为多个触发器。 多个触发器之间的执行顺序并不确定。 我个人认为应该避免使用多个触发器，因为它不利于维护。 查看某个触发器的内
35、容 use 数据库名 go exec sp_helptext 触发器名称 将会以表的样式显示触发器内容。 除了触发器外，sp_helptext 还可以显示 规则、默认值、未加密的存储过程、用户定义函数、视图的文本 用企业管理器查看 在表上点右键-“所有任务”-“管理触发器” ，选择所要查看的触发器 查看当前数据库中有哪些触发器 在查询分析器中运行： use 数据库名 go select * from sysobjects where xtype=TR sysobjects 保存着数据库的对象，其中 xtype 为 TR 的记录即为触发器对象。在 name 一列，我们可以看到触发器名称。 Nex
36、tPage删除触发器 用查询分析器删除 在查询分析器中使用 drop trigger 触发器名称 来删除触发器。 也可以同时删除多个触发器：drop trigger 触发器名称,触发器名称. 注意：触发器名称是不加引号的。在删除触发器之前可以先看一下触发器是否存在： if Exists(select name from sysobjects where name=触发器名称 and xtype=TR) 用企业管理器删除 在企业管理器中，在表上点右键-“所有任务”-“管理触发器” ，选中所要删除的触发器，然后点击“删除” 。 重命名触发器 用查询分析器重命名 exec sp_rename 原名称
37、, 新名称 sp_rename 是 SQL Server 自带的一个存储过程，用于更改当前数据库中用户创建的对象- 10 -的名称，如表名、列表、索引名等。 用企业管理器重命名 在表上点右键-“所有任务”-“管理触发器” ，选中所要重命名的触发器，修改触发器语句中的触发器名称，点击“确定” 。 查看触发器的属性存储过程 sp_helptrigger 用于查看触发器的属性。 sp_helptrigger 有两个参数：第一个参数为表名；第二个为触发器类型，为 char(6) 类型，可以是 INSERT、UPDATE、DELETE，如果省略则显示指定表中所有类型触发器的属性。 例： use 数据库名
38、 go exec sp_helptrigger tbl NextPage触发器更多语法 INSTEAD OF 执行触发器语句，但不执行触发触发器的 SQL 语句，比如试图删除一条记录时，将执行触发器指定的语句，此时不再执行 delete 语句。例： create trigger f on tbl instead of delete as insert into Logs. IF UPDATE(列名) 检查是否更新了某一列，用于 insert 或 update，不能用于 delete。例： create trigger f on tbl for update as if update(statu
39、s) or update(title) sql_statement -更新了 status 或 title 列 inserted、deleted 这是两个虚拟表，inserted 保存的是 insert 或 update 之后所影响的记录形成的表，deleted 保存的是 delete 或 update 之前所影响的记录形成的表。例： create trigger tbl_delete on tbl for delete as declare title varchar(200) select title=title from deleted insert into Logs(logContent) values(删除了 title 为： + title + 的记录) 说明：如果向 inserted 或 deleted 虚拟表中取字段类型为 text、image 的字段值时，所取得的值将会是 null。 NextPage慎用触发器触发器功能强大，轻松可靠地实现许多复杂的功能，为什么又要慎用呢。 触发器本身没有
