Sql Server数据库触发器实例讲解文章出处.doc

Sql Server数据库触发器实例讲解文章出处 定义： 何为触发器？在SQL Server里面也就是对某一个表的一定的操作，触发某种条件，从而执行的一段程序。触发器是一个特殊的存储过程。 常见的触发器有三种：分别应用于Insert , Update , Delete 事件。(SQL Server 2000定义了新的触发器，这里不提） 我为什么要使用触发器？比如，这么两个表： Create Table Student( -学生表 StudentID int primary key, -学号 ) Create Table BorrowRecord( -学生借书记录表 BorrowRecord int identity(1,1), -流水号 StudentID int , -学号 BorrowDate datetime, -借出时间 ReturnDAte Datetime, -归还时间 . ) 用到的功能有: 1.如果我更改了学生的学号,我希望他的借书记录仍然与这个学生相关(也就是同时更改借书记录表的学号); 2.如果该学生已经毕业，我希望删除他的学号的同时，也删除它的借书记录。 等等。 这时候可以用到触发器。对于1，创建一个Update触发器： Create Trigger truStudent On Student -在Student表中创建触发器 for Update -为什么事件触发 As -事件触发后所要做的事情 if Update(StudentID) begin Update BorrowRecord Set StudentID=i.StudentID From BorrowRecord br , Deleted d ,Inserted i -Deleted和Inserted临时表 Where br.StudentID=d.StudentID end 理解触发器里面的两个临时的表：Deleted , Inserted 。注意Deleted 与Inserted分别表示触发事件的表“旧的一条记录”和“新的一条记录”。 一个数据库系统中有两个虚拟表用于存储在表中记录改动的信息，分别是： 虚拟表Inserted 虚拟表Deleted在表记录新增时 存放新增的记录 不存储记录 修改时 存放用来更新的新记录 存放更新前的记录 删除时 不存储记录 存放被删除的记录 一个Update 的过程可以看作为：生成新的记录到Inserted表，复制旧的记录到Deleted表，然后删除Student记录并写入新纪录。 对于2，创建一个Delete触发器 Create trigger trdStudent On Student for Delete As Delete BorrowRecord From BorrowRecord br , Delted d Where br.StudentID=d.StudentID 从这两个例子我们可以看到了触发器的关键：A.2个临时的表；B.触发机制。 这里我们只讲解最简单的触发器。复杂的容后说明。事实上，我不鼓励使用触发器。触发器的初始设计思想，已经被“级联”所替代SQLSERVER触发器触发器是数据库应用中的重用工具，它的应用很广泛。这几天写一个化学数据统计方面的软件，需要根据采样，自动计算方差，在这里，我使用了触发器。 可以定义一个无论何时用INSERT语句向表中插入数据时都会执行的触发器。 当触发INSERT触发器时，新的数据行就会被插入到触发器表和inserted表中。inserted表是一个逻辑表，它包含了已经插入的数据行的一个副本。inserted表包含了INSERT语句中已记录的插入动作。inserted表还允许引用由初始化INSERT语句而产生的日志数据。触发器通过检查inserted表来确定是否执行触发器动作或如何执行它。inserted表中的行总是触发器表中一行或多行的副本。 日志记录了所有修改数据的动作(INSERT、UPDATE和DELETE语句)，但在事务日志中的信息是不可读的。然而，inserted表允许你引用由INSERT语句引起的日志变化，这样就可以将插入数据与发生的变化进行比较，来验证它们或采取进一步的动作。也可以直接引用插入的数据，而不必将它们存储到变量中。 示例 在本例中，将创建一个触发器。无论何时订购产品（无论何时向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 ON P.ProductID = I.ProductID DELETE触发器的工作过程 当触发DELETE触发器后，从受影响的表中删除的行将被放置到一个特殊的deleted表中。deleted表是一个逻辑表，它保留已被删除数据行的一个副本。deleted表还允许引用由初始化DELETE语句产生的日志数据。 使用DELETE触发器时，需要考虑以下的事项和原则： 当某行被添加到deleted表中时，它就不再存在于数据库表中；因此，deleted表和数据库表没有相同的行。 创建deleted表时，空间是从内存中分配的。deleted表总是被存储在高速缓存中。 为DELETE动作定义的触发器并不执行TRUNCATE TABLE语句，原因在于日志不记录TRUNCATE TABLE语句。 示例 在本例中，将创建一个触发器，无论何时删除一个产品类别（即从Categories表中删除一条记录），该触发器都会更新Products表中的Discontinued列。所有受影响的产品都标记为1，标示不再使用这些产品了。 USE Northwind CREATE TRIGGER Category_Delete ON Categories FOR DELETE AS UPDATE P SET Discontinued = 1 FROM Products 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表以及被更新的表，来确定是否更新了多行以及如何执行触发器动作。 可以使用IF UPDATE语句定义一个监视指定列的数据更新的触发器。这样，就可以让触发器容易的隔离出特定列的活动。当它检测到指定列已经更新时，触发器就会进一步执行适当的动作，例如发出错误信息指出该列不能更新，或者根据新的更新的列值执行一系列的动作语句。 语法 IF UPDATE () 例1 本例阻止用户修改Employees表中的EmployeeID列。 USE Northwind GO CREATE TRIGGER Employee_Update ON Employees FOR UPDATE AS IF UPDATE (EmployeeID) BEGIN RAISERROR (Transaction 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触发器被用于更新那些没有办法通过正常方式更新的视图。例如，通常不能在一个基于连接的视图上进行DELETE操作。然而，可以编写一个INSTEAD OF DELETE触发器来实现删除。上述触发器可以访问那些如果视图是一个真正的表时已经被删除的数据行。将被删除的行存储在一个名为deleted的工作表中，就像AFTER触发器一样。相似地，在UPDATE INSTEAD OF触发器或者INSERT INSTEAD OF触发器中，你可以访问inserted表中的新行。 不能在带有WITH CHECK OPTION定义的视图中创建INSTEAD OF触发器。示例 在本例中，创建了一个德国客户表和一个墨西哥客户表。放置在视图上的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 CustomersGer 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 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 Phone = 030-007xxxx WHERE CustomerID = ALFKI 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.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语句。当在定义有触发器的表上执行UPDATE语句时，原始行（前像）被移入到deleted表，更新行（后像）被移入到inserted表。触发器检查deleted表和inserted表以及被更新的表，来确定是否更新了多行以及如何执行触发器动作。初识SQLSERVER触发器(一)触发器是一种特殊的存储过程，类似于其它编程语言中的事件函数，SQL Server? 允许为 INSERT、UPDATE、DELETE 创建触发器，当在表（视图）中插入、更新、删除记录时，触发一个或一系列 T-SQL 语句。 本专题的目的并非是向您详尽地介绍触发器，而是系统地向您介绍触发器的常用知识与相关应用，并与您一起探讨应用经验，如果您需要了解触发器不常用的知识点，请参见 SQL Server? 联机丛书（安装 SQL Server? 时默认是安装的）。 a.基础创建一个简单的触发器 触发器是一种特殊的存储过程，类似于事件函数，SQL Server? 允许为 INSERT、UPDATE、DELETE 创建触发器，即当在表中插入、更新、删除记录时，触发一个或一系列 T-SQL语句。 触发器可以在查询分析器里创建，也可以在表名上点右键-“所有任务”-“管理触发器”来创建，不过都是要写 T-SQL 语句的，只是在查询分析器里要先确定当前操作的数据库。创建触发器用 CREATE TRIGGERCREATE TRIGGER 触发器名称ON 表名FOR INSERT、UPDATE 或 DELETEAS T-SQL 语句注意：触发器名称是不加引号的。如下是联机丛书上的一个示例，当在 titles 表上更改记录时，发送邮件通知 MaryM。CREATETRIGGERreminderONtitlesFORINSERT,UPDATE,DELETEASEXECmaster.xp_sendmailMaryM,Dontforgettoprintareportforthedistributors.b.基础-删除触发器 用查询分析器删除 在查询分析器中使用 drop trigger 触发器名称 来删除触发器。 也可以同时删除多个触发器：drop trigger 触发器名称,触发器名称. 注意：触发器名称是不加引号的。在删除触发器之前可以先看一下触发器是否存在： if Exists(select name from sysobjects where name=触发器名称 and xtype=TR) 用企业管理器删除 在企业管理器中，在表上点右键-“所有任务”-“管理触发器”，选中所要删除的触发器，然后点击“删除”。c.基础-重命名触发器 用查询分析器重命名 exec sp_rename 原名称, 新名称sp_rename 是 SQL Server? 自带的一个存储过程，用于更改当前数据库中用户创建的对象的名称，如表名、列表、索引名等。 用企业管理器重命名在表上点右键-“所有任务”-“管理触发器”，选中所要重命名的触发器，修改触发器语句中的触发器名称，点击“确定”。d.复杂一点的触发器 INSTEAD OF执行触发器语句，但不执行触发触发器的 SQL 语句，比如试图删除一条记录时，将执行触发器指定的语句，此时不再执行 delete 语句。例： createtriggerfontblinsteadofdeleteasinsertintoLogsIF UPDATE(列名)检查是否更新了某一列，用于 insert 或 update，不能用于 delete。例：createtriggerfontblforupdateasifupdate(status)orupdate(title)sql_statement-更新了status或title列inserted、deleted这是两个虚拟表，inserted 保存的是 insert 或 update 之后所影响的记录形成的表，deleted 保存的是 delete 或 update 之前所影响的记录形成的表。例：createtriggertbl_deleteontblfordeleteasdeclaretitlevarchar(200)selecttitle=titlefromdeletedinsertintoLogs(logContent)values(删除了title为：+title+的记录)说明：如果向 inserted 或 deleted 虚拟表中取字段类型为 text、image 的字段值时，所取得的值将会是null。sql server:触发器 收藏 触发器是一种特殊的存储过程，类似于其它编程语言中的事件函数，SQL Server 允许为 INSERT、UPDATE、DELETE 创建触发器，当在表（视图）中插入、更新、删除记录时，触发一个或一系列 T-SQL 语句。 本专题的目的并非是向您详尽地介绍触发器，而是系统地向您介绍触发器的常用知识与相关应用，并与您一起探讨应用经验，如果您需要了解触发器不常用的知识点，请参见 SQL Server 联机丛书（安装 SQL Server 时默认是安装的）。 在视图上创建普通触发器可能会出现“对象无效”的错误，实际上，我们不能在视图上创建 FOR 触发器，而应该创建 INSTEAD OF 触发器。在 SQL Server 联机丛书中，是没有说触发器不能在视图上创建的， 并且在语法解释中表明：在 CREATE TRIGGER 的 ON 之后可以是视图。 然而，事实似乎并不是如此，很多专家也说触发器不能在视图上创建。 我也专门作了测试，的确如此，不管是普通视图还是索引视图，都无法在上面创建触发器. 但是无可厚非的是：当在临时表或系统表上创建触发器时会遭到拒绝。FOR CREATE TRIGGER 语句FOR 关键字之后可以跟 INSERT、UPDATE、DELETE 中的一个或多个，也就是说在其它情况下是不会触发触发器的， 包括 SELECT、TRUNCATE、WRITETEXT、UPDATETEXT。TRUNCATE TABLE 和不带 WHERE 的 DELETE 功能是一样的，都是删除表中的所有数据，不过 TRUNCATE TABLE 速度更快，占用的日志更少，这是因为 TRUNCATE TABLE 直接释放数据页并且在事务日志中也只记录数据页的释放，而 DELETE 是一行一行地删除，在事务日志中要记录每一条记录的删除。 那么可不可以用 TRUNCATE TABLE 代替不带 WHERE 的 DELETE 呢？在以下情况是不行的： 1、要保留标识的情况下不能用 TRUNCATE TABLE，因为 TRUNCATE TABLE 会重置标识。 2、需要使用触发器的情况下不能使用 TRUNCATE TABLE ，它不会激发触发器。 3、对于由 FOREIGN KEY 约束引用的表（即主键所在的表，不是外键所在的表）不能使用 TRUNCATE TABLE。 4、对于参与了索引视图的表不能使用 TRUNCATE TABLE ，注意指索引视图，并非普通视图。 触发器内部语句出错时，前面对数据更改操作将会无效。 举个例子，在表中插入数据时触发触发器，而触发器内部此时发生了运行时错误，那么将返回一个错误值，并且拒绝刚才的数据插入。 NextPage一个有趣的触发器应用 触发器回滚我们看到许多注册系统在注册后都不能更改用户名，但这多半是由应用程序决定的， 如果直接打开数据库表进行更改，同样可以更改其用户名，在触发器中利用回滚就可以巧妙地实现无法更改用户名。 use 数据库名 go create trigger tr on 表名 for update as if update(userName) rollback tran 关键在最后两句，其解释为：如果更新了 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 LOG。 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. 嵌套触发器 类似于间接递归，间接递归必然要形成一个环，而嵌套触发器不一定要形成一个环，它可以 T1-T2-T3.这样一直触发下去，最多允许嵌套 32 层。 - 设置直接递归 默认情况下是禁止直接递归的，要设置为允许有两种方法： T-SQL：exec sp_dboption dbName, recursive triggers, true EM：数据库上点右键-属性-选项。 设置间接递归、嵌套 默认情况下是允许间接递归、嵌套的，要设置为禁止有两种方法： T-SQL：exec sp_configure nested triggers, 0 -第二个参数为 1 则为允许 EM：注册上点右键-属性-服务器设置。 递归触发器 递归分两种，间接递归和直接递归。我们举例解释如下，假如有表1、表2名称分别为 T1、T2，在 T1、T2 上分别有触发器 G1、G2。 间接递归：对 T1 操作从而触发 G1，G1 对 T2 操作从而触发 G2，G2 对 T1 操作从而再次触发 G1. 直接递归：对 T1 操作从而触发 G1，G1 对 T1 操作从而再次触发 G1. 嵌套触发器 类似于间接递归，间接递归必然要形成一个环，而嵌套触发器不一定要形成一个环，它可以 T1-T2-T3.这样一直触发下去，最多允许嵌套 32 层。 - 设置直接递归 默认情况下是禁止直接递归的，要设置为允许有两种方法： T-SQL：exec sp_dboption dbName, recursive triggers, true EM：数据库上点右键-属性-选项。 设置间接递归、嵌套 默认情况下是允许间接递归、嵌套的，要设置为禁止有两种方法： T-SQL：exec sp_configure nested triggers, 0 -第二个参数为 1 则为允许 EM：注册上点右键-属性-服务器设置。 NextPage多个触发器 触发器的名称不同，触发事件相同（INSERT、UPDATE、DELETE），我们称为多个触发器。 多个触发器之间的执行顺序并不确定。 我个人认为应该避免使用多个触发器，因为它不利于维护。 查看某个触发器的内容 use 数据库名 go exec sp_helptext 触发器名称 将会以表的样式显示触发器内容。 除了触发器外，sp_helptext 还可以显示 规则、默认值、未加密的存储过程、用户定义函数、视图的文本 用企业管理器查看 在表上点右键-“所有任务”-“管理触发器”，选择所要查看的触发器 查看当前数据库中有哪些触发器 在查询分析器中运行： use 数据库名 go select * from sysobjects where xtype=TR sysobjects 保存着数据库的对象，其中 xtype 为 TR 的记录即为触发器对象。在 name 一列，我们可以看到触发器名称。 NextPage删除触发器 用查询分析器删除 在查询分析器中使用 drop trigger 触发器名称 来删除触发器。 也可以同时删除多个触发器：drop trigger 触发器名称,触发器名称. 注意：触发器名称是不加引号的。在删除触发器之前可以先看一下触发器是否存在： if Exists(select name from sysobjects where name=触发器名称 and xtype=TR) 用企业管理器删除 在企业管理器中，在表上点右键-“所有任务”-“管理触发器”，选中所要删除的触发器，然后点击“删除”。 重命名触发器 用查询分析器重命名 exec sp_rename 原名称, 新名称 sp_rename 是 SQL Server 自带的一个存储过程，用于更改当前数据库中用户创建的对象的名称，如表名、列表、索引名等。 用企业管理器重命名 在表上点右键-“所有任务”-“管理触发器”，选中所要重命名的触发器，修改触发器语句中的触发器名称，点击“确定”。 查看触发器的属性存储过程 sp_helptrigger 用于查看触发器的属性。 sp_helptrigger 有两个参数：第一个参数为表名；第二个为触发器类型，为 char(6) 类型，可以是 INSERT、UPDATE、DELETE，如果省略则显示指定表中所有类型触发器的属性。 例： use 数据库名 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(status) 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慎用触发器触发器功能强大，轻松可靠地实现许多复杂的功能，为什么又要慎用呢。 触发器本身没有过错，但由于我们的滥用会造成数据库及应用程序的维护困难。 在数据库操作中，我们可以通过关系、触发器、存储过程、应用程序等来实现数据操作，比如删除 T1 表记录时期望删除 T2 表相关的记录，此时可以建立级联删除的关系，也可以为 T1 表建立触发器使同时删除 T2 表相关记录，也可以自定义存储过程删除 T1 和 T2 表的记录，也可以在应用程序中使用两个 SQL 语句来删除到底用哪一种好呢，应该说我们建立通过建立关系来实现级联删除是最好的，除非更有高的需求。 触发器还有一个用途可以用来保障数据的完整性，但同时规则、约束、默认值也可以保障数据完整性，到底哪一个好呢，一般说来，较为简单的完整性要求我们不应该使用触发器。两者在运行机制上也是有区别的，像规则、约束、默认值这些是在数据更改之前进行数据验证，而触发器是在数据更改之后进行验证（如果事务回滚，该表将不会产生变化）。 总之，如果我们对触发器过分的依赖，就会造成遍地是程序的情况，因为触发器本身就需要别的程序给它一个触发条件，也就是说至少在两个地方存在着程序，同时我们抛弃了约束、默认值等而选用触发器，势必影响数据库的结构。SqlServer触发器的应用一、insert触发器Sql代码 createtriggertr_COMMISSIONINFO_INSERTonCOMMISSION_INFO_ forinsert as begin declareCOMMISSIONIDvarchar(20),DATEdatetime setCOMMISSIONID=(selectID_fromINSERTED) setDATE=getdate() insertintoCOOPERATE_YEARS_(COMMISSION_ID_,CONTRACT_START_DATE_,RE_CONTRACT_START_DATE_)VALUES(COMMISSIONID,DATE,DATE) endcreate trigger tr_COMMISSIONINFO_INSERT on COMMISSION_INFO_ for insert as begin declare COMMISSIONID varchar(20),DATE datetime set COMMISSIONID=(select ID_ from INSERTED) set DATE=getdate()insert into COOPERATE_YEARS_(COMMISSION_ID_, CONTRACT_START_DATE_, RE_CONTRACT_START_DATE_) VALUES(COMMISSIONID, DATE, DATE) end二、update触发器Sql代码 createtriggertr_COMMISSIONINFO_UPDATEonCOMMISSION_INFO_ forupdate as begin declareCOMMISSIONIDvarchar(20),OLD_COMM_CATEGORYint,NEW_COMM_CATEGORYint,DATEdatetime setCOMMISSIONID=(selectID_fromINSERTED) setOLD_COMM_CATEGORY=(selectCOMM_CAGEGORY_fromDELETED) setNEW_COMM_CATEGORY=(selectCOMM_CAGEGORY_fromINSERTED) setDATE=getdate() if(OLD_COMM_CATEGORY106)and(NEW_COMM_CATEGORY=103andNEW_COMM_CATEGORY=106) beginifexists(select1fromCOOPERATE_YEARS_whereCOMMISSION_ID_=COMMISSIONID) beginupdateCOOPERATE_YEARS_setRE_CONTRACT_START_DATE_=DATEwhereCOMMISSION_ID_=COMMISSIONID endelsebegininsertintoCOOPERATE_YEARS_(COMMISSION_ID_,CONTRACT_START_DATE_,RE_CONTRACT_START_DATE_)VALUES(COMMISSIONID,DATE,DATE) endendendsqlserver触发器 收藏 一 触发器是一种特殊的存储过程它不能被显式地调用而是在往表中插入记录更新记录或者删除记录时被自动地激活。所以触发器可以用来实现对表实施复杂的完整性约束。 二 SQL Server为每个触发器都创建了两个专用表Inserted表和Deleted表。这两个表由系统来维护它们存在于内存中而不是在数据库中。这两个表的结构总是与被该触发器作用的表的结构相同。触发器执行 完成后与该触发器相关的这两个表也被删除。Deleted表存放由于执行Delete或Update语句而要从表中删除的所有行。Inserted表存放由于执行Insert或Update语句而要向表中插入的所有行。三Instead of 和 After触发器SQL Server2000提供了两种触发器Instead of 和After 触发器。这两种触发器的差别在于他们被激活的同 Instead of触发器用于替代引起触发器执行的T-SQL语句。除表之外Instead of 触发器也可以用于视图用来扩展视图可以支持的更新操作。 After触发器在一个Insert,Update或Deleted语句之后执行进行约束检查等动作都在After触发器被激活之前发生。After触发器只能用于表。 一个表或视图的每一个修改动作(insert,update和delete)都可以有一个instead of 触发器一个表的每个修改动作都可以有多个After触发器。四触发器的执行过程如果一个Insertupdate或者delete语句违反了约束那幺After触发器不会执行因为对约束的检查是在After触发器被激动之前发生的。所以After触发器不能超越约束。 Instead of 触发器可以取代激发它的操作来执行。它在Inserted表和Deleted表刚刚建立其它任何操作还没有发生时被执行。因为Instead of 触发器在约束之前执行所以它可以对约束进行一些预处理。 五使用T-SQL语句来创建触发器 基本语句如下 create trigger trigger_name on table_name | view_name for | After | Instead of insert, update,delete as sql_statement 六相关示例 1在Orders表中建立触发器当向Orders表中插入一条订单记录时检查goods表的货品状态status是否为1(正在整理)是则不能往Orders表加入该订单。create trigger orderinserton ordersafter insertas begin transactionif (select status from goods,insertedwhere =inserted.goodsname)=1beginprint the goods is being processedprint the order cannot be committedrollback transaction -回滚避免加入end2在Orders表建立一个插入触发器在添加一条订单时减少Goods表相应的货品记录中的库存。create trigger orderinsert1on ordersafter insertasbegin transactionupdate goods set storage=storage-inserted.quantityfrom goods,=inserted.goodsname3在Goods表建立删除触发器实现Goods表和Orders表的级联删除。create trigger goodsdeleteon goodsafter deleteasbegin transactiondelete from orders where goodsname in(select name from deleted)4在Orders表建立一个更新触发器监视Orders表的订单日期(OrderDate)列使其不能手工修改.create trigger orderdateupdateon ordersafter updateasbegin transactionif update(orderdate)beginraiserror( orderdate cannot be modified,10,1)rollback transactionend5在Orders表建立一个插入触发器保证向Orders表插入的货品名必须要在Goods表中一定存在。c