触发器原理及使用

《触发器原理及使用》由会员分享，可在线阅读，更多相关《触发器原理及使用（35页珍藏版）》请在金锄头文库上搜索。

1、触发器原理及使用触发器原理及使用第 13 章 触发器原理及使用在上一章我们介绍了一般意义的存储过程，即用户自定义的存储过程和系统存储过程。本章将介绍一种特殊的存储过程，即触发器。以下几个分几个部分对触发器的概念、作用、工作原理以及触发器的设计和使用做尽介绍，使读者了解如何定义触发器，创建和使用各种不同复杂程度的触发器。131 触发器基本概念 1311 触发器的概念及作用触发器可以看作是一类特殊的存储过程，它在满足某个特定条件时自动触发执行。 触发器是为表上的更新、插入、删除操作定义的，也就是说当表上发生更新、插入或删除操作时触发器将执行。存储过程和触发器同是提高数据库服务器性能的有力工具。触发

2、器作为一种特殊类型的存储过程，它不同于我们前面介绍过的存储过程。触发器主要是通过事件进行触发而被执行的，而存储过程可以通过存储过程名字而被直接调用。当对某一表进行诸如UPDATE、 INSERT、 DELETE 这些操作时，SQL Server 就会自动执行触发器所定义的 SQL 语句，从而确保对数据的处理必须符合由这些 SQL 语句所定义的规则。触发器的主要作用就是其能够实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性。除此之外，触发器还有其它许多不同的功能：（1） 强化约束（Enforce restriction）触发器可以侦测数据库内的操作，从而不允许数据库中未经许可的更新和变

3、化。（2）级联运行（Cascaded Operation）触发器可以侦测数据库内的操作，并自动地级联影响整个数据库的各项内容。例如，某个表上的触发器包含对另外一个表的数据操作（如删除、更新、插入） ，而该操作又导致该表上的触发器被触发。（3）存储过程的调用(Stored Procedure Invocation)为了响应数据库更新，触发器可以调用一个或多个存储过程，甚至可以通过外部过程的调用而在 DBMS 之外进行操作。由此可见，触发器可以解决高级形式的业务规则或复杂行为限制以及实现定制记录等问题。例如，触发器能够找出某一表在数据修改前后状态发生的差异，并根据这种差异执行一定的操作。此外一个表

4、的同一类型（INSERT、UPDATE、DELETE）的多个触发器能够对同一种数据操作采取多种不同的操作。触发器可以用于维护数据参照完整性和以下一些场合： （1）触发器可以通过级联的方式对相关的表进行修改。比如，对父表的修改，可以引起对子孙表的一系列修改，从而保证数据的一致性和完整性。 （2）触发器可以禁止或撤消违反参照完整性的修改。 （3）触发器可以强制比用 CHECK 约束定义更加复杂的限制。 触发器也是一个数据库对象。一个触发器和三部分内容有关：激活触发器的表、激活触发器的数据修改语句和触发器要采取的动作。 但是，触发器性能通常比较低。当运行触发器时，系统处理的大部分时间花费在参照其它表

5、的这一处理上，因为这些表既不在内存中也不在数据库设备上，而删除表和插入表总是位于内存中。可见触发器所参照的其它表的位置决定了操作要花费的时间长短。1312 触发器的种类SQL Server 2000 支持两种类型的触发器：AFTER 触发器和 INSTEAD OF 触发器。1）AFTER 触发器即为 SQL Server 2000 版本以前所介绍的触发器。该类型触发器要求只有执行完某一操作（INSERT、UPDATE、DELETE） ，并处理过所有约束后，触发器才被触发，且只能在表上定义。如果操作违反约束条件，将导致事务回滚，这时就不会执行后触发器。可以为针对表的同一操作定义多个 AFTER

6、触发器。AFTER 触发器可以指定哪一个触发器被最先触发，哪一个被最后触发，通常使用系统过程 sp_settriggerorder 来完成此任务。2）INSTEAD OF 触发器该类触发器表示并不执行其所定义的操作（INSERT、UPDATE、DELETE） ，而仅是执行触发器本身。既可在表上定义 INSTEAD OF 触发器，也可以在视图上定义 INSTEAD OF 触发器，但对同一操作只能定义一个 INSTEAD OF 触发器。132 触发器原理从以上的介绍中我们可以已了解到触发器具有强大的功能，那么 MS SQL Server 是如何使得触发器能够感知数据库数据的变化、维护数据库参照完整

7、性及比 CHECK 约束更复杂的约束呢？下面我们将对其工作原理及实现做较为详细的介绍，以便大家学习创建、理解和使用各种类型的触发器，完成各种任务。每个触发器有两个特殊的表：插入表和删除表，分别为 inserted和 deleted。有以下几个特点：1）这两个表是逻辑表，并且这两个表是由系统管理的，存储在内存中，不是存储在数据库中，因此不允许用户直接对其修改。2）这两个表的结构总是与被该触发器作用的表有相同的表结构。3）这两个表是动态驻留在内存中的，当触发器工作完成，这两个表也被删除。这两个表主要保存因用户操作而被影响到的原数据值或新数据值。4）另外，这两个表是只读的，且只在触发器内部可读，即用

8、户不能向这两个表写入内容，但可以在触发器中引用表中的数据。例如在触发器内可用如下语句查看 DELETED 表中的信息：Select * from deleted 下面详细介绍这两个表的功能。13.2.1 插入表的功能（inserted）对一个定义了插入类型触发器的表来讲，一旦对该表执行了插入（INSERT）操作，那么对该表插入的所有行来说，都有一个相应的副本级存放到插入表（inserted）中，即插入表就是用来存储原表插入的新数据行。13.2.2 删除表的功能（deleted）对一个定义了删除类型触发器的表来讲，一旦对该表执行了删除（DELETE）操作，则将所有的被删除的行存放至删除表（del

9、eted表）中。这样做的目的是，一旦触发器遇到了强迫它中止的语句被执行时，删除的那些行可以从删除表(deleted 表)中得以还原。 需要强调的是，更新（UPDATE）操作包括两个部分，即先将旧的内容删除，然后将新值插入。因此，对一个定义了更新类型触发器的表来讲，当执行更新操作时，在删除表中存放了修改之前的旧值，然后在插入表中存放的是修改之后的新值。由于触发器仅当被定义的操作被执行时才被激活，即仅当在执行插入、删除、和更新操作时，触发器将执行。每条 SQL 语句仅能激活触发器一次，可能存在一条语句影响多条记录的情况。在这种情况下就需要变量rowcount 的值，该变量存储了一条 SQL 语句执

10、行后所影响的记录数，可以使用该值对触发器的 SQL 语句执行后所影响的记录求合计值。一般来说，首先要用 IF 语句测试rowcount 的值以确定后面的语句是否执行。13.2.3插入视图和删除视图当在定义了触发器的表上发生修改操作时会自动派生出两个视图，一个是插入视图，一个是删除视图。当在表上发生插入操作时，新插入的行将出现在 inserted 表中形成插入视图；当在表上发生删除操作时，被删除的旧行将出现 deleted 表中，形成删除视图。而更新的实现过程是先删除旧行，然后再插入新行。133 触发器的创建和管理1331 创建触发器上面介绍了有关触发器的概念、作用和工作原理，下面我们将分别介绍

11、在 MS SQLServer 中如何用 SQL Server 管理工具 Enterprise Manager 和 Transaction_SQL 来创建触发器。在创建触发器以前必须考虑到以下几个方面： ?CREATE TRIGGER 语句必须是批处理的第一个语句，将该批处理中随后的其它所有语句解释为 CREATE TRIGGER 语句定义的一部分；?创建触发器的权限默认分配给表的所有者，且不能将该权限转给其他用户； ?触发器是数据库对象，所以其命名必须符合命名规则； ?尽管在触发器的 SQL 语句中可以引用其它数据库中的对象，但是，触发器只能创建在当前数据库中； ?虽然不能在临时表或系统表上创

12、建触发器，但是触发器可以引用临时表。不应引用系统表，而应使用信息架构视图。有关更多信息，请参见第 9 章关于信息架构视图的内容。?一个触发器只能对应一个表，这是由触发器的机制决定的；?在含有用 DELETE 或 UPDATE 操作定义的外键的表中，不能定义 INSTEAD OF 和 INSTEAD OF UPDATE 触发器。 ?尽管 TRUNCATE TABLE 语句如同没有 WHERE 从句的 DELETE 语句，但是由于 TRUNCATE TABLE 语句没有被记入日志，所以该语句不能触发 DELETE 型触发器； ?WRITETEXT 语句不能触发 INSERT 或 UPDATE 型的

13、触发器。 ?在触发器定义中，所有建立和更改数据库以及数据库对象的语句、所有的 drop 语句都不允许在触发器中使用。?在触发器定义中，可使用 IF UPDATE 子句来测试INSERT、UPDATE 语句中是否对指定字段有影响。如果将一个值赋给指定字段或更改了指定的字段，则这个子句为真。?通常不要在触发器中返回任何结果，因此不要在触发器定义中使用 select 语句或变量赋值语句。当创建一个触发器时，必须指定触发器的名字，在哪一个表上定义触发器，激活触发器的修改语句，如 INSERT、 DELETE、 UPDATE。 当然两个或三个不同的修改语句也可以都触发同一个触发器，如INSERT 和 U

14、PDATE 语句都能激活同一个触发器。1、用 Enterprise Manger 创建触发器的步骤如下：（1）启动 Enterprise Manager,登录到要使用的服务器。（2）在 Enterprise Manager 的左窗格中，展开要创建触发器的数据库文件夹， 单击“表”文件夹，此时在右窗格中显示该数据库的所有表。（3）在右窗格中，右击要创建触发器的数据表，在弹出的快捷菜单中，将鼠标指向“所有任务” ，在出现的下一级子菜单中选择“管理触发器”菜单项，此时会出现 “触发器属性”对话框。图 13-1 选择“管理触发器”菜单项图 13-2 “触发器属性”对话框（4）在“名称”下拉框中选择“”

15、 ， “文本”编辑框中输入触发器的文本命令。图 13-3 输入触发器名字和 T-SQL 文本（5）单击上图所示的“检查语法”按钮，检查语句是否正确。如果正确转第（6）部，否则进行修改（6）单击“应用”按钮，在“名称”下拉列框中会显示新创建的触发器名字。2、用 CREATE TRIGGER 命令创建触发器可用 CREATE TRIGGER 命令创建触发器，其语法规则如下CREATE TRIGGER trigger_name ON table | view WITH ENCRYPTION FOR | AFTER | INSTEAD OF INSERT , UPDATE WITH APPEND NO

16、T FOR REPLICATION AS IF UPDATE ( column ) AND | OR UPDATE ( column ) .n | IF ( COLUMNS_UPDATED ( ) bitwise_operator updated_bitmask ) comparison_operator column_bitmask .n Sql 语句 .n 从以上语句可以看出，一个表最多可以有三类触发器：插入（INSERT）触发器、更新（UPDATE）触发器、删除（DELETE）触发器。一个触发器只能应用到一个表上，但一个触发器可以包含很多动作，可以执行很多功能。各参数的说明如下：（1）trigger_name 是用户要创建的触发器的名字。触发器的名字必须符合 SQL Server 的命名规则，且其名字在当前数据库中必须是惟一的。 （2）tablel|view 是与用户创建的触发器相关联的表的