触发器学习资料

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

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

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

4、PDATE WITH 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语句。用SQL Ent

5、erprise 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 properties”窗口，如图34 所示，在该窗口中输入触发器代

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

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

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

9、器检查用户可以使用触发器检查事务。例如，创建一个名为TRJ的触发器，如图35所示。该触发器的功能是：将删除的工程项目数据转移到存档工程表JBACK中（假设已建立了与工程表J结构相同的存档表JBACK），,我们可以如下定义触发器：CREATE TRIGGER TRJ ON JFOR DELETEASINSERT JBACK SELECT JNO,JNAME,JCITY,BALANCE FROM deleted又如：创建一个名为TRSPJ_UPDATE_PRICE的触发器，使其具有如下功能：在修改供应表(SPJ)的单价时,要求修改后的单价一定要比原来的单价低。,我们可以如下定义触发器：CREATE

10、 TRIGGER TRSPJ_UPDATE_PRICE ON SPJ FOR UPDATEAS IF(SELECT COUNT(*) FROM deleted,inserted WHERE deleted.price>=inserted.price)=0ROLLBACK TRANSACTION图16 .35 触发器TRJ代码6、instead of 和 after 触发器 的区别主要包括定义和应用范围条件，操作执行时机； AFTER 触发器（也叫“FOR”触发器）会在触发 insert、update 或是delect 动作之后执行。例如，一个 Employees 表上的 AFTER 触发

11、器会在在 Employee 表上执行一条 update 语句后激活。因此，AFTER 触发器只有在已插入一行或是多行和所有约束已被处理且通过后才触发。INSTEAD OF 触发器和 AFTER 触发器有本质上的不同，因为 INSTEAD OF 触发器代替触发动作进行激发。就拿同样的例子来说，如果在 Emplyees 表上有一个 INSTEAD OF UPDATE 触发器和在这个表上执行一条 UPDATE 语句，结果是这条 UPDATE 语句并不会改变 Employee 表中的任何一行。相反,这条 UPDATE 语句只有是为了踢离 INSTEAD OF UPDATE 触发器，这个触发器可能会，也

12、可能不会改变 Employees 表中的数据。因此，怎么决定在合适的时间和位置放置 INSTEAD OF 触发器呢？有几个关键的因素在做决定是值得考虑的。AFTER 触发器多用在动作必须在表中数据发生改变之后才执行后情情况。比如，AFTER 触发器可以用于将对数据作任何变动的日志记录在一个相对独立的审计表中。INTEAD OF 触发器也能做同样的工作。但是 INSTEAD OF 触发器在这个情况下的效率比较低，因为更新动作只能在将它发生的动作准确地记录在审计表之后才允许执行。一般来说，只要不影响数据的修改，AFTER 触发器比 INSTEAD OF 触发器更有效率。在对数据进行计算或是对数据的修改作为一个整体提交或是作为一个整体回退的情况下，AFTER 触发器也是一个很好的选择。例如，存在这样一条规则：对在 Products 表的产品价格的变动超过30的必须回退。AFTE