数据库设计（7/9）：触发器

对于设计和创建数据库完全是个新手？没关系，Joe Celko， 世界上读者数量最多的SQL作者之一，会告诉你这些基础。和往常一样，即使是最专业的数据库老手，也会给他们带来惊喜。Joe是DMBS杂志是多年来最受 读者喜爱的作者。他在美国、英国，北欧，南美及非洲传授SQL知识。他在ANSI / ISO SQL标准委员会工作了10年，为SQL-89和SQL-92标准做出了杰出贡献。

---

在第1篇到第4篇，我们创建了表，架构的基础和可视化。第5篇和第6篇讲了存储过程。这篇文章会讲述在触发器上，你需要尽量避免的特性。

通常你会被告知，触发器是存储过程的“特殊类别”，但这不完全对。它没有参数，你不能触发它，它有其它地方不存在本地伪表（local pseudo-tables）。在我们详细讨论前，我们停下来，讨论下在基础表里，修改数据的SQL模型。

INSERT,UPDATE和DELETE

SQL是面向集合语言，因此它用数据集合同时修改表。它不是面向记录语言，它会一次修改一条记录来处理数据，按顺序来（想下磁带和打孔卡）。修改表内容的3个基本操作是INSERT，UPDATE和DELETE。

插入是它们最容易理解的。这是概念上的模型，不是实际的实现。你把行集合放入现存表。但比这更多。这个集合一次作为整个单元，因此用CURRENT_TIMESTAMP这样常量的任何函数调用，对于所有的新行有同样的值。标识（IDENTITY）表性质（性质，不是列），当在增加表级别计数器的时候，通过查询行和尝试写入违反了这个模型。IDENTITY值不确定性，取决于在插入时索引和 机械装置的物理状态。

在ANSI/ISO标准和T-SQL的INSERT里，新行保存在伪表里，命名为新。如果插入会把表引入违反任何约束的状态，那么事务会被系统回滚，你会收到错误信息。那就是说新行绝不会插入到表（但是，标识属性会增加）。

删除是类似的。在ANSI/ISO标准和T-SQL里DELETE里，在基表里符合DELETE FROM语句的WHERE字句的所有行，放入伪表里，命名为旧。如果删除会把表引入违法任何约束的状态，那么事务会被系统回滚，你会收到错误信息。那就是说旧行绝不离开表。

实际上，大多数SQL引擎会在一次通过里标记旧行，在最后通过里检查约束并移除它们。

更新是删除和插入的组合。概念模型是我们在WHERE子句上查找并创建删除伪表，就像一个DELETE FROM。然后我们看SET子句上创建新的要插入的伪表。

表由行组成，行由列组成。在SET子句里每个赋值同时完成。如果一列从SET子句里丢失，SQL引擎实际上创建一个“SET <列名>=<列名>"--什么也不做的赋值。这不像过程化语言从左到右处理。这个语句会在列a和列b里交换值，且一次完成。

UPDATE Foobar
SET a = b,
     b = a;

这个过程语句会把列a和列b设置为同样的值，因为它从左到右执行。

 BEGIN
 SET a = b;
 SET b = a;
 END;

删除行会被移除，插入行在表里持续，所有都一次完成。如果更新会把表引入违反任何约束的状态，那么事务会被系统回滚，你会收到错误信息。那就是说表绝不修改。

数据库事件

触发器是附加到一个且只有一个表的代码。但一个表可以有多个触发器。有CREATE TRIGGER语句，因为它是持续架构对象。基本的T-SQL语法非常直接：

CREATE TRIGGER <trigger name>
ON <table or view name>
{FOR | AFTER | INSTEAD OF} {[INSERT] [,] [UPDATE] [,] [DELETE]}
AS
<trigger body>;

trigger name和table name或view name就是对应的对象名称。AFTER和FOR是等价的关键字，但AFTER更有描述行（其它SQL会BEFORE触发器）。稍后我们会讲解INSTEAD OF选项。

INSERT，UPDATE，DELETE被称为数据库事件或动作。SELECT语句，DROP和ALTER不是数据库事件。当在表上这些操作中的一个完成，在数据库动作成功完成后，触发器会被触发。对于每个数据库事件，触发器主体里的代码在表层级上一次执行。

注意。你的程序更新表T1，它触发触发器TR1，它更新表T1本身。因为表T1被更新，触发器TR1再次触发，如此循坏。可能毫无休止。但它不需要在那里停止。你的触发器可以引起在其它表上触发器触发。触发器也可以内嵌的。假设程序更新表T1，触发了触发器TR1，它更新了表T2。在T2上的触发器触发，再次更新表T1。这个模式可以扩展到多个表，不停止循环。

SQL引擎需要跟踪所有这些修改，这样的话如果有东西出错的话，它可以进行回滚。避免写这样的代码：它耗资源，运行缓慢且会锁掉整个数据库。它也很难维护。

触发器主体

触发器主体是一块T-SQL过程化语句块。但有一点区别。你不能穿参数给触发器主体，像一个存储过程。触发器主体会访问INSERTED和DELETED伪表。如果你想要的话，可以重命名这些伪表。

这个格式也有特殊的逻辑功能：UPDATE（<列名>）和COLUMNS_UPDATED（）。这些测试来看一个UPDATE FROM或INSERT INTO语句在他们的参数列表里是否修改一个或多个列。这是专有功能，因此接下来我会详细讲解，一个简答的例子会是：

CREATE TRIGGER No_Embezzlement_Trigger
ON Payroll
AFTER UPDATE
AS
IF UPDATE(payroll_amt)
BEGIN
RAISEERROR ('You cannot give yourself a raise');
ROLLBACK TRANSACTION;
END;

INSTEAD OF触发器

更新视图被熟知为一个NP完全问题。在英语里，那是说，我们知道在合理的次数里没有常规的方式来完成它，就如问题变得越来越大。

INSTEAD OF触发器是我们更新视图并回避RDBMS的算法限制。一个NSTEAD OF触发器执行触发器主体，不是它触发的数据库动作。这用例子来解释更容易。假设我们有以在两个表上有join和聚合函数定义的视图：

CREATE VIEW SalesSummary (order_nbr, order_amt_tot)
AS
SELECT O.order_nbr, O.customer_name, SUM(D.unit_price * D.order_qty)
  FROM Orders AS O, Order_Details AS D
 WHERE O.order_nbr = D.order_nbr
 GROUP BY O.order_nbr;

如果对SalesSummary数据库事件有个触发器，不使用INSTEAD OF触发器的话它会失败。这个视图有join，计算和聚合来确保它是不可更新的。但INSTEAD OF触发器不是一个前触发器！真正的前触发器会执行它的代码，然后尝试完成数据库事件。INSTEAD OF触发器会独自执行它的代码并完成。

记住视图是虚拟的：它物理且持续不存在。那就是说你没有已删除和已插入的伪表来使用。再进一步，你不能从INSERT INTO，DELETE FROM或UPDATE语句里拿到参数。所有代码需要在基础表上操作。

INSTEAD OF触发器也可以和视图一样运行在基础表上。通常它不这样用。T-SQL习惯上进行AFTER触发器，检查下结果和修正，或者如果有问题的话回滚。

审计触发器

使用触发器的一个常见技巧是在一个或多个表里收集审计数据。通常认为这不是个好主意。它通过增加额外的读写降低了应用的性能。对于SELECT语句没有触发器，因此你不能跟踪谁在查看数据。健康保险携带和责任法案（HIPAA (Health Insurance Portability and Accountability Act)）和许多其它法律需要对每个查看的数据都有记录。

但不止这些，审计的基本原则是审计从被审计的事物分离。例如，当一个采购订单创建了一个货运，负责运输的人和同意这份订单的不是同一个人。发运到你本人的诱惑避免这个方法。

假设表上有列用户事件日期和雇员更新记录的用户id，这由触发器来完成。直到你考虑到它，这才听起来不错。触发器一直在，不会被推翻。哎呀！如果你删除了行，审计数据也一起删掉。如果有人可以访问审计列，它们会被更新为任何值。

第三方审计工具使用事务日志文件，它是服务器为恢复和用来捕获所有需要审计的动作的网络带宽创建，来保持数据安全，物理上从剩下的数据库分离。这会通过法律测试。记住ROI在今天的美国里，表示”监禁的危险（Risk of Incarceration）“，不是”投资回报率（return on investment）“。

数据和引用完整性触发器

如果你有旧的SQL代码迁移到新发布的SQL，你可以看下是否有触发器可以用DRI（引用完整性）约束和动作来替换。这是触发器的初衷。例如，在订单表里一个订单被删除，触发器会删除订单明细表里的所有相关记录。可以问下前辈，当我们忘记触发器或用错它们，花了多少时间来找无关联的行。

现在，这些完整性触发器的大部分可以用声明DRI操作来代替。它们对DELETE和UPDATE动作进行简单的动作。这个动作是在DDL上的选项子句。完整语法是：

FOREIGN KEY (<referencing table column list>)
 REFERENCES <referenced table name> (<referenced table column list>)
[ON UPDATE | ON DELETE][NO ACTION | CASCADE | SET NULL | SET DEFAULT]

NO ACTION：一个错误信息告诉用户这个操作不允许，我们得到一个回滚。

CASCADE：在外键关系里删除或更新所有涉及到行数据。

SET NULL：设置引用列为NULL。这假设对于表，所有外键列允许接受NULL。

SET DEFAULT:这是引用表列为定义的默认值。这假设对于表，所有列有定义的默认值。

只有在完整性规则复杂的时候才使用触发器。一个我能想到的，当一个成员插入或删除时，在多个组涉及值的重发布的例子。即使那样，考虑把它放入存储过程。

T-SQL对DDL触发器也有扩展。这些被DDL事件触发，而不是DML事件——CREATE, ALTER, DROP, GRANT, DENY, REVOKE 或 UPDATE STATISTICS语句。

同个事件的多个触发器

对于同个数据库事件有不止一个触发器是合法的。这不是个好主意，但在T-SQL里是合法的。尝试在一个触发器里完成，这样的话容易维护。

默认情况下，在SQL Server表里，对于同个操作的多个触发器是不确定的。但是，对于两个AFTER触发器使用系统存储过程settriggerorder声明触发顺序还是可能的。这个存储过程不能用在INSTEAD OF触发器。

语法非常直接：

EXEC sp_settriggerorder
@triggername = <explains itself>,
@order = [FIRST|LAST|NONE], -- firing order
@stmttype = [INSERT|UPDATE|DELETE], --trigger type
@namespace = [DATABASE|SERVER|NULL] ; -- explains itself

参数@order表示触发起是否第一个还是最有一个触发。如果指定NONE，那么没有强制顺序，我们回到了默认的状态。显然你不能有2个FIRST或2个LAST触发器。

但是，如果你有第3个触发器，它不是FIRST，也不是LAST，那它肯定在触发顺序里的中间。

小结

各位，不止T-SQL，有触发器的专有实现。因此它们不迁移。它们行为的一些可以是非确定性的，因此它们很难调试。它们不告诉优化器它可以使用的任何东西，像DRI动作做的。可是你很有可能会发现，它们是确认复杂数据完整行规则的最安全方法。偶第神啊！

原文链接

http://www.sqlservercentral.com/articles/Stairway+Series/71822/