触发器学习资料.doc

1、触发器触发器是 SQL Server 提供给程序员和数据库分析员确保数据完整性的一种方法。这些方法对于那些经常被大量的不同应用程序访问的数据库相当有用，因为它们使数据库增强了应用规则，而应用规则是依赖于应用软件的。1 SQL Server 触发器的概念SQL Server 有效地管理信息的能力来源于它在系统中控制数据的能力。存储过程的建立，使用户能够在服务器上执行逻辑，通过规则和默认值去帮助数据库更进一步地管理信息。SQL Server 在信息被写入数据库之前确认规则和默认值。这对于信息是一种“预过滤器” ，并且能基于数据项控制数据库活动的作用阻止数据项的活动。触发器是在数据更新后执行的“后置

2、过滤器” ，并且 SOL Server 已经确认了这些规则、默认值等。触发器是 SQL Server 执行的特殊类型的存储过程，它发生在对于一个给定表的插入、修改或删除操作执行后。由于触发器是在操作有效执行后才被运行，在修改中它们代表“最后动作” 。假如触发器导致的一个请求失败的话，SOL Server 将拒绝信息更新，并且对那些倾向于事务处理的应用程序返回一个错误消息。触发器最普遍的应用是实施数据库中的商务规则，当然在维持引用完整性方面，外键要比触发器更快，但触发器能够维持那些外键所不能处理的复杂关系。触发器不会明显影响服务器的性能。它们经常被用于增强那些在其他的表和行上进行很多级联操作的应

3、用程序的功能。2 创建触发器创建触发器的用户必须是该数据库的拥有者，当添加一个触发器到列、行或表的时候，就会改变怎样使表能够被访问，怎样使其他对象能够与之关联等。因此，实际上正在改变数据库的模式。当然这种类型的操作为数据库拥有者所保留，以便防止有人无意中修改了系统的布局格式。创建触发器相当于说明一个存储过程，并且它有相似的语法。 创建触发器的语法：CREATE TRIGGER trigger_nameON tableWITH ENCRYPTIONFOR DELETE,INSERT,UPDATEWITH APPENDNOT FOR REPLICATIONASSql_statement nFOR

4、INSERT,UPDATEWITH APPENDNOT FOR REPLICATIONAS IF UPDATE(column)ANDORUPDATE(column)nIF （COLUMNS_UPDATED（）bitwise_operator）updated_bitmask)comparison_operatorcolumn_bitmasknsql_statementn上述语句中的 trigger_name 为所定义的触发器名称;关键字 INSERT,UPDATE ,DELETE 定义了触发器的作用域，后者决定了启动触发器的操作; Sql_statement 为包含在触发器中的任何合法的 SQL

5、 语句。用 SQL Enterprise Manager 来创建触发器在 Windows 开始菜单中执行“程序|Microsoft SQL Server 7.0|Enterprise Manager”命令，进入 Enterprise Manager 界面。在 Enterprise Manager 中展开 SQL Server Group,再展开 Database 项，选择要创建触发器的数据库（如PROJECT） ，再选中要创建触发器的表（如 J） ，按右键，在弹出的菜单上选择 All Tasks 命令，再选 Manage Triggers 命令,如图 33 所示，弹出一个“Trigger pr

6、operties”窗口，如图 34 所示，在该窗口中输入触发器代码。3Inserted 和 deleted 表当触发器被执行时，SQL Server 创建一个或两个临时表（Inserted 或者deleted 表） 。当一个记录插入到表中时，相应的插入触发器创建一个 inserted 表，该表镜像该触发器相连接的表的列结构。例如，当用户在 J 表中插入一行时，J表的触发器使用 J 表的列结构创建 inserted 表。对于插入到 J 表的每一行，相应地在 inserted 表中也包括该行。deleted 表也镜象触发器相连接的表的列结构。当执行一条 DELETE 语句时从表中删除的每一行都包含

7、在删除触发器内的 deleted 表中。图 16.33 选择 Manage Triggers被 UPDATE 语句触发的触发器创建两个表 inserted 和 deleted 表，这两个表和它们相连接的表有相同的列结构。deleted 和 inserted 表分别包含相连接表中数据的“前后”快照。例如，假设用户执行下面的语句：UPDATE J SET JNO=J10 WHERE JNO=S8图 16.34 Triggers Properties 窗口当该语句被执行时，J 表中的更新触发器被触发。在触发器的 inserted 和deleted 表中，该语句所改变的每一数据行都在这两个表中包含一行

8、。deleted 表中行的数据值是执行 UPDATE 语句之前的 J 表中行的数据值；inserted 表中则是执行 UPDATE 语句后的 J 表中行的数据值。4Update()函数触发器降低了 SQL Server 事务的性能并且在事务执行时保持锁处于打开状态。如果用户的触发器逻辑只有当某些特定列改变时才需要运行，用户就应该检测这种情况的出现。update（）函数可以帮助用户进行检测。Update（）函数只在插入和更新触发器中可用，它确定用户传递给它的列是否已经被引起触发器激活的 insert 或 update 语句所作用。例如在表 J 上定义更新触发器，使其阻止 JNO 列被执行，触发器

9、定义如下：CREATE TRIGGER TRJ_Upd ON JFOR UPDATEASIF update（JNO）ROLLBACK TRANSACTIONRETURN5触发器检查用户可以使用触发器检查事务。例如，创建一个名为 TRJ 的触发器，如图 35 所示。该触发器的功能是：将删除的工程项目数据转移到存档工程表 JBACK 中（假设已建立了与工程表 J 结构相同的存档表 JBACK） ，,我们可以如下定义触发器：CREATE TRIGGER TRJ ON JFOR DELETEASINSERT JBACKSELECT JNO,JNAME,JCITY,BALANCEFROM deleted

10、又如：创建一个名为 TRSPJ_UPDATE_PRICE 的触发器，使其具有如下功能：在修改供应表(SPJ)的单价时,要求修改后的单价一定要比原来的单价低。,我们可以如下定义触发器：CREATE TRIGGER TRSPJ_UPDATE_PRICE ON SPJ FOR UPDATEAS IF(SELECT COUNT(*)FROM deleted,insertedWHERE deleted.price=inserted.price)=0ROLLBACK TRANSACTION6、instead of 和 after 触发器 的区别主要包括定义和应用范围条件，操作执行时机； AFTER 触发器

11、（也叫“FOR”触发器）会在触发 insert、update 或是 delect 动作之后执行。例如，一个 Employees 表上的 AFTER 触发器会在在 Employee 表上执行一条 update 语句后激活。因此，AFTER 触发器只有在已插入一行或是多行和所有约束已被处理且通过后才触发。INSTEAD OF 触发器和 AFTER 触发器有本质上的不同，因为 INSTEAD OF 触发器代替触发动作进行激发。就拿同样的例子来说，如果在 Emplyees 表上有一个 INSTEAD OF UPDATE 触发器和在这个表上执行一条 UPDATE 语句，结果是这条 UPDATE 语句并图

12、 16 .35 触发器 TRJ 代码不会改变 Employee 表中的任何一行。相反,这条 UPDATE 语句只有是为了踢离 INSTEAD OF UPDATE 触发器，这个触发器可能会，也可能不会改变 Employees 表中的数据。因此，怎么决定在合适的时间和位置放置 INSTEAD OF 触发器呢？有几个关键的因素在做决定是值得考虑的。AFTER 触发器多用在动作必须在表中数据发生改变之后才执行后情情况。比如，AFTER 触发器可以用于将对数据作任何变动的日志记录在一个相对独立的审计表中。INTEAD OF 触发器也能做同样的工作。但是 INSTEAD OF 触发器在这个情况下的效率比较

13、低，因为更新动作只能在将它发生的动作准确地记录在审计表之后才允许执行。一般来说，只要不影响数据的修改，AFTER 触发器比 INSTEAD OF 触发器更有效率。在对数据进行计算或是对数据的修改作为一个整体提交或是作为一个整体回退的情况下，AFTER 触发器也是一个很好的选择。例如，存在这样一条规则：对在 Products 表的产品价格的变动超过 30的必须回退。 AFTER 触发器能很漂亮地完成这个工作，它利用已插入同已删除的表中的产品价格作比较，然后在有必要的时回滚事务。这些都是 AFTER 触发器的理想条件，但有时 INSTEAD OF 会更好些。INSTEAD OF 触发器有一个很大的特点 就是它允许你在某个表或视图上用多个复杂的查询操作来代替单一的查询。跟 AFTER 触发器只能对表起作用不同，INSTEAD OF 触发器可以同时对表和视图起作用。