第13章 触发器原理及使用
在上一章我们介绍了一般意义的存储过程，即用户自定义的存储过程和系统存储过程。本章将介绍一种特殊的存储过程，即触发器。以下几个分几个部分对触发器的概念、作用、工作原理以及触发器的设计和使用做尽介绍，使读者了解如何定义触发器，创建和使用各种不同复杂程度的触发器。
13．1 触发器基本概念
13．1．1 触发器的概念及作用
触发器可以看作是一类特殊的存储过程，它在满足某个特定条件时自动触发执行。 触发器是为表上的更新、插入、删除操作定义的，也就是说当表上发生更新、插入或删除操作时触发器将执行。存储过程和触发器同是提高数据库服务器性能的有力工具。
触发器作为一种特殊类型的存储过程，它不同于我们前面介绍过的存储过程。触发器主要是通过事件进行触发而被执行的，而存储过程可以通过存储过程名字而被直接调用。当对某一表进行诸如UPDATE、 INSERT、 DELETE 这些操作时，SQL Server 就会自动执行触发器所定义的SQL 语句，从而确保对数据的处理必须符合由这些SQL 语句所定义的规则。
触发器的主要作用就是其能够实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性。除此
之外，触发器还有其它许多不同的功能：
（1） 强化约束（Enforce restriction）
触发器可以侦测数据库内的操作，从而不允许数据库中未经许可的更新和变化。
（2）级联运行（Cascaded Operation）
触发器可以侦测数据库内的操作，并自动地级联影响整个数据库的各项内容。例如，某个表上的触发器包含对另外一个表的数据操作（如删除、更新、插入），而该操作又导致该表上的触发器被触发。
（3）存储过程的调用(Stored Procedure Invocation)
为了响应数据库更新，触发器可以调用一个或多个存储过程，甚至可以通过外部过程的调用而在DBMS之外进行操作。
由此可见，触发器可以解决高级形式的业务规则或复杂行为限制以及实现定制记录等问题。例如，触发器能够出某一表在数据修改前后状态发生的差异，并根据这种差异执行一定的操作。此外一个表的同一类型（INSERT、UPDATE、DELETE）的多个触发器能够对同一种数据操作采取多种不同的操作。
触发器可以用于维护数据参照完整性和以下一些场合：
（1）触发器可以通过级联的方式对相关的表进行修改。比如，对父表的修改，可以引起对子孙表的一系列修改，从而保证数据的一致性和完整性。
（2）触发器可以禁止或撤消违反参照完整性的修改。
（3）触发器可以强制比用CHECK约束定义更加复杂的限制。
触发器也是一个数据库对象。
一个触发器和三部分内容有关：激活触发器的表、激活触发器的数据修改语句和触发器要采取的动作。
但是，触发器性能通常比较低。当运行触发器时，系统处理的大部分时间花费在参照其它表的这一处理上，因为这些表既不在内存中也不在数据库设备上，而删除表和插入表总是位于内存中。可见触发器所参照的其它表的位置决定了操作要花费的时间长短。
13．1．2 触发器的种类
SQL Server 2000 支持两种类型的触发器：AFTER触发器和INSTEAD OF触发器。
1）AFTER触发器
即为SQL Server 2000版本以前所介绍的触发器。该类型触发器要求只有执行完某一操作（INSERT、UPDATE、DELETE），并处理过所有约束后，触发器才被触发，且只能在表上定义。如果操作违反约束条件，将导致事务回滚，这时就不会执行后触发器。
可以为针对表的同一操作定义多个AFTER触发器。AFTER触发器可以指定哪一个触发器被最先触发，哪一个被最后触发，通常使用系统过程sp_settriggerorder来完成此任务。
2）INSTEAD OF触发器
该类触发器表示并不执行其所定义的操作（INSERT、UPDATE、DELETE），而仅是执行触发器本身。既可在表上定义INSTEAD OF触发器，也可以在视图上定义INSTEAD OF触发器，但对同一操作只能定义一个INSTEAD OF触发器。
13．2 触发器原理
从以上的介绍中我们可以已了解到触发器具有强大的功能，那么MS SQL Server 是如何使得触发器能够感知数据库数据的变化、维护数据库参照完整性及比CHECK约束更复杂的约束呢？下面我们将对其工作原理及实现做较为详细的介绍，以便大家学习创建、理解和使用各种类型的触发器，完成各种任
务。
每个触发器有两个特殊的表：插入表和删除表，分别为inserted和deleted。有以下几个特点：
1）这两个表是逻辑表，并且这两个表是由系统管理的，存储在内存中，不是存储在数据库中，因此不允许用户直接对其修改。
2）这两个表的结构总是与被该触发器作用的表有相同的表结构。
3）这两个表是动态驻留在内存中的，当触发器工作完成，这两个表也被删除。这两个表主要保存因用户操作而被影响到的原数据值或新数据值。
4）另外，这两个表是只读的，且只在触发器内部可读，即用户不能向这两个表写入内容，但可以在触发器中引用表中的数据。例如在触发器内可用如下语句查看DELETED表中的信息：
Select * from deleted
下面详细介绍这两个表的功能。
13.2.1 插入表的功能（inserted）
对一个定义了插入类型触发器的表来讲，一旦对该表执行了插入（INSERT）操作，那么对该表插入的所有行来说，都有一个相应的副本级存放到插入表（inserted）中，即
插入表就是用来存储原表插入的新数据行。
13.2.2 删除表的功能（deleted）
对一个定义了删除类型触发器的表来讲，一旦对该表执行了删除（DELETE）操作，则将所有的被删除的行存放至删除表（deleted表）中。这样做的目的是，一旦触发器遇到了强迫它中止的语句被执行时，删除的那些行可以从删除表(deleted表)中得以还原。        sql server 2000是一种
需要强调的是，更新（UPDATE）操作包括两个部分，即先将旧的内容删除，然后将新值插入。因此，对一个定义了更新类型触发器的表来讲，当执行更新操作时，在删除表中存放了修改之前的旧值，然后在插入表中存放的是修改之后的新值。
由于触发器仅当被定义的操作被执行时才被激活，即仅当在执行插入、删除、和更新操作时，触发器将执行。每条SQL 语句仅能激活触发器一次，可能存在一条语句影响多条记录的情况。在这种情况下就需要变量@@rowcount 的值，该变量存储了一条SQL 语句执行后所影响的记录数，可以使用该值对触发器的SQL 语句执行后所影响的记录求合计值。一般来说，首先要用IF 语句测试@@rowcount 的值以确定后面的语句是否执行。
13.2.3．插入视图和删除视图
当在定义了触发器的表上发生修改操作时会自动派生出两个视图，一个是插入视图，一个是删除视图。当在表上发生插入操作时，新插入的行将出现在inserted表中形成插入视图；当在表上发生删除操作时，被删除的旧行将出现deleted表中，形成删除视图。而更新的实现过程是先删除旧行，然后再插入新行。
13．3 触发器的创建和管理
13．3．1 创建触发器
上面介绍了有关触发器的概念、作用和工作原理，下面我们将分别介绍在MS SQLServer 中如何用SQL Server 管理工具Enterprise Manager 和Transaction_SQL 来创建触发器。
在创建触发器以前必须考虑到以下几个方面：
? CREATE TRIGGER 语句必须是批处理的第一个语句，将该批处理中随后的其它所有语句解释为 CREATE TRIGGER 语句定义的一部分；
? 创建触发器的权限默认分配给表的所有者，且不能将该权限转给其他用户；
? 触发器是数据库对象，所以其命名必须符合命名规则；
? 尽管在触发器的SQL 语句中可以引用其它数据库中的对象，但是，触发器只能创建在当前数据库中；
? 虽然不能在临时表或系统表上创建触发器，但是触发器可以引用临时表。不应引用系统表，而应使用信息架构视图。有关更多信息，请参见第9章关于信息架构视图的内容。
? 一个触发器只能对应一个表，这是由触发器的机制决定的；
? 在含有用 DELETE 或 UPDATE 操作定义的外键的表中，不能定义 INSTEAD OF 和 INSTEAD OF UPDATE 触发器。
? 尽管TRUNCATE TABLE 语句如同没有WHERE 从句的DELETE 语句，但是由于TRUNCATE TABLE 语句没有被记入日志，所以该语句不能触发DELETE 型触发器；
? WRITETEXT 语句不能触发INSERT 或UPDATE 型的触发器。
? 在触发器定义中，所有建立和更改数据库以及数据库对象的语句、所有的drop语句都不允许在触发器中使用。
? 在触发器定义中，可使用IF UPDATE子句来测试INSERT、UPDATE语句中是否对指定字段有影响。如果将一个值赋给指定字段或更改了指定的字段，则这个子句为真。
? 通常不要在触发器中返回任何结果，因此不要在触发器定义中使用select语句或变量赋值语句。
当创建一个触发器时，必须指定触发器的名字，在哪一个表上定义触发器，激活触发器的修改语句，如INSERT、 DELETE、 UPDATE。 当然两个或三个不同的修改语句也可以都触发同一个触发器，如INSERT 和UPDATE 语句都能激活同一个触发器。
1、用Enterprise Manger创建触发器的步骤如下：
（1）启动Enterprise Manager，登录到要使用的服务器。
（2）在Enterprise Manager的左窗格中，展开要创建触发器的数据库文件夹， 单击“表”文件夹，此时在右窗格中显示该数据库的所有表。
（3）在右窗格中，右击要创建触发器的数据表，在弹出的快捷菜单中，将鼠标指向“所有任务”，在出现的下一级子菜单中选择“管理触发器”菜单项，此时会出现 “触发器属性”对话框。
图13-1 选择“管理触发器”菜单项
图13-2 “触发器属性”对话框
（4）在“名称”下拉框中选择“<；新建>”，“文本”编辑框中输入触发器的文本命令。
图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 ]
[ NOT 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是与用户创建的触发器相关联的表的名字或视图的名称，并且此表或视图必须已经存在。
（3）WITH ENCRYPTION 表示对包含有CREATE TRIGGER 文本的syscomments 表进行加密。
（4）AFTER 表示只有在执行了指定的操作（INSERT、 DELETE、 UPDATE）之后触发器才被激活，
所有的引用级联操作和约束检查也必须成功完成后，才能执行触发器中的SQL 语句。指定触发器只有在触发 SQL 语句中指定的所有操作都已成功执行后才激发。若使用关键字FOR， 则表示为AFTER 触发器，且该类型触发器仅能在表上创建，不能在视图上定义 AFTER 触发器。
（5）INSTEAD OF 指定执行触发器而不是执行触发 SQL 语句，从而替代触发语句的操作。在表或视图上，每个 INSERT、UPDATE 或 DELETE 语句最多可以定义一个 INSTEAD OF 触发器。然而，可以在每个具有 INSTEAD OF 触发器的视图上定义视图。INSTEAD OF 触发器不能在 WITH CHECK OPTION 的可更新视图上定义。如果向指定了 WITH CHECK OPTION 选项的可更新视图添加 INSTEAD OF 触发器，SQL Server 将产生一个错误。用户必须用 ALTER VIEW 删除该选项后才能定义 INSTEAD OF 触发器。
（6）[DELETE] [,] [INSERT] [,] [UPDATE] 关键字用来指明哪种数据操作将激活触发器。至少要指明其中的一个选项，在触发器的定义中，三者的顺序不受限制，各选项要用逗号隔开。对于 INSTEAD OF 触发器，不允许在具有 ON DELETE 级联操作引用关系的表上使用 DELETE 选项。同样，也不允许在具有 ON UPDATE 级联操作引用关系的表上使用 UPDATE 选项。
（7）WITH APPEND表明增加另外一个已存在的触发器。只有在兼容性水平（指某一数据库行为与以前版本的SQL Server兼容程度）不大于65时才使用该选项。WITH APPEND 不能与 INSTEAD OF 触
发器一起使用，或者，如果显式声明 AFTER 触发器，也不能使用该子句。只有当出于向后兼容而指定 FOR 时（没有 INSTEAD OF 或 AFTER），才能使用 WITH APPEND。以后的版本将不支持 WITH APPEND 和 FOR（将被解释为 AFTER）。
（8）NOT FOR REPLICATION 表明当复制处理修改与触发器相关联的表时，触发器不能被执行。
（9）AS是触发器将要执行的动作。
（10）sql语句 是包含在触发器中的条件语句和处理语句。触发器的条件语句定义了另外的标准来决定将被执行的INSERT、 DELETE、 UPDATE 语句是否激活触发器。当尝试 DELETE、INSERT 或 UPDATE 操作时，Transact-SQL语句中指定的触发器操作将生效。触发器可以包含任意数量和种类的 Transact-SQL 语句。触发器旨在根据数据修改语句检查或更改数据；它不应将数据返回给用户。触发器中的 T
ransact-SQL 语句常常包含控制流语言。CREATE TRIGGER 语句中使用两个特殊的表：deleted 和 inserted 表，它们是逻辑（概念）表。这些表在结构上类似于定义触发器的表，用于保存用户操作可能更改的行的旧值或新值。
（11）IF UPDATE （column） 测试在指定的列上进行的 INSERT 或 UPDATE 操作，不能用于 DELE
TE 操作。可以指定多列。因为在 ON 子句中指定了表名，所以在 IF UPDATE 子句中的列名前不要包含表名。若要测试在多个列上进行的 INSERT 或 UPDATE 操作，请在第一个操作后指定单独的 UPDATE(column) 子句。在 INSERT 操作中 IF UPDATE 将返回 TRUE 值，因为这些列插入了显式值或隐性 (NULL) 值。
说明：IF UPDATE (column) 子句的功能等同于 IF、IF...ELSE 或 WHILE 语句，并且可以使用 END 语句块。可以在触发器主体中的任意位置使用 UPDATE (column)。其中column是要测试 INSERT 或 UPDATE 操作的列名。该列可以是 SQL Server 支持的任何数据类型，但是，计算列不能用于该环境中。
（12）IF （COLUMNS_UPDATED（）） 仅在INSERT 和UPDATE 类型的触发器中使用，用其来检查所涉及的列是被更新还是被插入。 COLUMNS_UPDATED 返回 varbinary 位模式，表示插入或更新了表中的哪些列。COLUMNS_UPDATED 函数以从左到右的顺序返回位，最左边的为最不重要的位。最左边的位表示表中的第一列；向右的下一位表示第二列，依此类推。如果在表上创建的触发器包含 8 列以上，则 COLUMNS_UPDATED 返回多个字节，最左边的为最不重要的字节。在 INSERT 操作中 COLUMNS_UPDATED 将对所有列返回 TRUE 值，因为这些列插入了显式值或隐性 (NULL) 值。可以在触发器主体中的任意位置使用 COLUMNS_UPDATED。
（13）Bitwise_operatorj 是在比较中使用的位逻辑运算符。
（14）Pdated_bitmask 是那些被更新或插入的列的整形位掩码。例如，如果表T 包括C1， C2， C3， C4， C5五列。为了确定是否只有C2 列被修改，可用2 来做位掩码，如果想确定是否C1， C3，C4 都被修改，可用13 来做位掩码。
（15）Comparison_operator 是一比较操作符，用“= ”表示检查在updated_bitmask 中定义的所有列是否都被更新，用“>” 表示检查是否在updated_bitmask 中定义的某些列被更新。
（16）Column_bitmask 指那些被检查是否被更新的列的位掩码。
例13-1 创建一个触发器，当向STUDENT表中插入一条学生记录时，自动显示该表中的记录。
CREATE TRIGGER Change_Display
On STUDENT FOR INSERT
AS
SELECT * FROM STUDENT
该触发器建立完毕后，当对STUDENT表的插入操作执行成功时，将会显示STUDENT数据表中的全部记录。
权限说明：CREATE TRIGGER 权限默认授予定义触发器的表所有

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。