第7章 触发器

第7章触发器触发器是一种特殊的存储过程，其特殊性在于它不需要由用户来直接调用，而是在对表中的数据进行UPDATE、INSERT或DELETE操作时自动触发执行的。

触发器通常用于保证业务规则和数据完整性，其主要优点是用户可以用编程的方法来实现复杂的处理逻辑和商业规则，增强了数据完整性约束的功能。

触发器通常用在下列场合：z完成比CHECK约束更复杂的数据约束。

与 CHECK 约束不同，触发器可以引用其它表中的列。

例如，触发器可以使用另一个表中的列来比较插入或更新的数据，也可以执行其它的操作，比如修改数据或显示用户定义的错误信息。

z为保证数据库性能而维护的非规范化数据。

比如，为提高数据的统计效率，在销售情况表中增加了统计销售总值的列（使销售情况表成为非规范化表），以后，每当在此表中插入数据时，都使用触发器统计销售总值列的新数值，并将统计后的新值保存在此表中。

以后每当有对销售总值的查询时，就直接从表中取数据即可，而不再需要使用查询语句再进行统计，从而提高数据的统计效率。

z可实现复杂的商业规则。

触发器可使业务的处理任务自动进行。

例如：在库存系统中，更新触发器可以检测什么时侯库存下降到了需要再进货的量，并自动生成给供货商的定单。

又例如：在记录工厂加工过程的数据库中，当某个加工过程超过所定义的安全限制时，可以使用触发器给操作员发出电子邮件或寻呼。

z触发器也可以评估数据修改前后的表状态，并根据其差异采取对策。

z一个表中的多个同类触发器（INSERT、UPDATE 或 DELETE）允许采取多个不同的对策以响应同一个修改语句。

7.1创建触发器建立触发器时，要指定触发器的名称、触发器所作用的表、引发触发器的操作以及在触发器中要完成的功能。

建立触发器有两种方法，一种是使用企业管理器，另一种是使用SQL语句。

我们这里只介绍使用SQL语句建立触发器的方法，使用企业管理器建立触发器的思想与用SQL语句是一样的。

1．创建触发器的语句建立触发器的SQL语句为：CREATE TRIGGER ，其语法格式为：CREATE TRIGGER 触发器名称ON {表名 | 视图名}[ WITH ENCRYPTION ]{ FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ DELETE ] [ , ] [UPDATE ] }AS[ { IF UPDATE ( column )[ { AND | OR } UPDATE ( column ) ...]SQL 语句其中：z触发器名称在数据库中必须是惟一的。

z ON子句用于指定在其上执行触发器的表。

z AFTER：指定触发器只有在触发SQL语句中指定的操作都已成功执行，并且所有的约束检查也成功完成后，才执行此触发器。

z FOR：作用同AFTER。

z INSTEAD OF：指定执行触发器而不是执行引发触发器执行的SQL语句，从而替代触发语句的操作。

注意：只有SQL Server 2000版本才开始支持INSTEAD OF触发器。

z INSERT、DELETE和UPDATE是引发触发器执行的操作，若同时指定多个操作，则各操作之间用逗号分隔。

创建触发器时，需要注意如下几点：（1） 在一个表上可以建立多个名称不同、类型各异的触发器，每个触发器可由所有三个操作来引发。

对于AFTER型的触发器，可以在一种操作上建立多个触发器；对于INSTEAD OF型的触发器，在一种操作上只能建立一个触发器。

（2） 大部分Transact-SQL语句都可用在触发器中，但也有一些限制。

例如，所有的建立和更改数据库以及数据库对象的语句、所有的DROP语句都不允许在触发器中使用。

（3） 在触发器定义中，可以使用IF UPDATE子句来测试在INSERT和UPDATE语句中是否对指定字段有影响。

如果将一个值赋给指定字段或更改了指定字段，则这个子句就为真。

（4） 通常不要在触发器中返回任何结果，因此不要在触发器定义中使用SELECT语句或变量赋值语句。

（5） 在触发器中可以使用两个特殊的临时表：INSERTED表和DELETED表。

前者保存了INSERT操作中新插入的数据和UPDATE操作中的更新后的数据，后者保存了DELETE 操作删除的数据和UPDATE操作中的更新前的数据。

在触发器中对这两个表的使用方法同一般基本表一样，可以通过这两个临时表所记录的数据来判断对数据的修改是否正确。

2．INSERTED表和DELETED表触发器语句中使用了两个特殊的临时工作表：INSERTED 表和DELETED表。

这两个表是在用户执行数据的更改操作时，SQL Server自动创建和管理的。

这两个表驻留在内存中，其结构同触发器所作用的基本表的结构，并且只可以被触发器使用，当触发器结束时，系统自动释放这两个表的空间。

在触发器中可以象普通表一样对这两个表进行查询，并作为测试某些数据修改的效果及设置触发器操作的条件。

但在触发器中不能直接对这两个逻辑表中的数据进行更改。

DELETED表用于存储 DELETE 和 UPDATE 语句所影响的行的复本。

在执行 DELETE 或 UPDATE 语句时，行从触发器表中删除，并传输到 DELETED表中。

DELETED表和触发器表通常没有相同的行。

INSERTED表用于存储 INSERT 和 UPDATE 语句所影响的行的副本。

在一个插入或更新操作中，新建行被同时添加到 INSERTED表和触发器表中。

INSERTED表中的行是触发器表中新行的副本。

更新操作类似于在删除之后执行插入：首先在触发器表中删除更新前的行，并将这些行复制到DELETED表中，然后将更新后的新行复制到触发器表和 INSERTED表中。

在设置触发器条件时，应当为引发触发器的操作恰当使用 INSERTED和 DELETED表。

虽然在测试INSERT操作时引用DELETED表或在测试DELETE操作时引用 INSERTED表不会引起任何错误，但是在这种情形下这些触发器测试表中不会包含任何数据记录。

在触发器中对这两种逻辑表的使用方法同一般基本表一样，可以通过这两个临时表所记录的数据来判断对数据的修改是否正确。

7.2 后触发的触发器使用FOR 或AFTER选项定义的触发器为后触发的触发器，即只有在引发触发器执行的语句中指定的操作都已成功执行，并且所有的约束检查也成功完成后，才执行此触发器。

注：不能在视图上定义AFTER触发器。

例1．创建带有提示信息的触发器。

每当用户在pubs数据库的jobs表中插入数据时，向客户产生一条提示信息。

CREATE TRIGGER tri_insert_jobsON SC FOR insertASPRINT '在jobs表中插入了数据'执行如下语句测试触发器的作用：INSERT INTO jobs V ALUES(100, 'writer', 80, 100)系统会显示：在jobs表中插入了数据7.2.1维护数据完整性约束的触发器1．维护不同列数据之间的取值完整性例2．创建实现数据完整性约束的触发器。

限制销售表（sales）中的订购日期（ord_date）必须晚于图书的出版日期（titles表中的pubdate）。

这种涉及两个表的不同列之间的取值完整性约束，可以用触发器来实现。

实现此约束的触发器代码为：CREATE TRIGGER tri_sales_orddateON salesFOR insert, updateASIF EXISTS(SELECT * FROM inserted a JOIN titles bON a.title_id = b.title_idWHERE ord_date < pubdate )BEGINPRINT '订购日期应晚于出版日期'ROLLBACKEND执行如下语句测试触发器的作用：INSERT INTO sales V ALUES('7896','P723','1983-03-11', 20, 'Net 30','BU1111')注：触发器与引发触发器执行的操作共同构成了一个事务，事务的开始是引发触发器执行的操作，事务的结束是触发器的结束。

由于AFTER型的触发器在执行时，引发触发器执行的操作已经执行完了，因此，在触发器中应使用rollback撤消不正确的操作，这里的rollback实际是回滚到引发触发器执行的操作之前的状态。

2．维护数据完整性——级联更新所谓级联更新是指当父表（主码所在的表）的主码值更新时，如果在子表（外码所在的表）中有对此主码值的引用，则将此外码值一起更新。

许多大型数据库管理系统都支持对数据的级联更新的功能，但在定义外码时，如果没有特殊指定，则对主码的更新是受限制的。

即如果在外码表中有对此主码值的引用，则限制更新此主码的值。

如果所使用的数据库管理系统不支持级联更新功能，则可以使用触发器实现级联更新操作。

例3．对pubs数据库，更改stores表中的商店编号（stor_id）列的值时，同时更改sales表中相应的stor_id列的值。

CREATE TRIGGER storid_updateON stores FOR updateASIF UPDATE(stor_id)BEGINIF EXISTS(SELECT * FROM deleted INNER JOIN salesON Deleted.stor_id = sales.stor_id)BEGINDECLARE @new_id intDECLARE @old_id intSelect @new_id = stor_id from insertedSelect @old_id = stor_id from deletedUPDATE sales SET stor_id = @new_idWHERE stor_id = @old_idENDEND注意：当两个表之间有外码约束时，默认情况下，不能通过触发器实现主码和其相关的表的外码的级联更新。

若要验证此触发器的执行，请按下述步骤执行：（1） 删除sales表与stores表的外码引用约束（因为系统是先检查约束，后执行触发器）；（2） 查看sales表和stores表中的数据，特别是商店号为“6380”的记录；（3） 执行如下更新语句：UPDATE stores SET stor_id = '1111' where stor_id = '6380'（4）再次查看sales表和stores表中的数据，特别观察商店号为“1111”的记录，可发现sales表中的商店id号为“6380”的记录现已变为“1111”7.2.2 维护数据一致性的触发器触发器的一个重要作用是维护非规范化数据的一致性，有时，为了提高数据的查询效率，我们会特意在表中保存一些统计数据，使规范化的表变成非规范化的。