探究 SQL Server 的触发器:第二部分

原文出处：MSDN Magazine Jan 2004(Data Points) 下载源代码：DataPoints0401.exe (111KB) 　　这个月我会继续上个月的专栏——“SQL SERVER触发器的基础知识及其特点” 。我开这个专栏的目的是为了回应我收到的许多关于触发器的问题。我会从 INSTEAD OF 触发器以及它与AFTER 触发器差别开始讲起。然后我会在讨论一些使用 INSTEAD OF 触发器的情形。其中包括允许几个视图对多个表进行更新。最后，我会讨论触发器怎样去处理事务，编写改变某个表中多行记录的触发器，以及确定在触发器中哪些列被修改。 INSTEAD OF 触发器 　　AFTER 触发器（也叫“FOR”触发器）会在触发 insert、update 或是delect 动作之后执行。例如，一个 Employees 表上的 AFTER 触发器会在在 Employee 表上执行一条 update 语句后激活。因此，AFTER 触发器只有在已插入一行或是多行和所有约束已被处理且通过后才触发。INSTEAD OF 触发器和 AFTER 触发器有本质上的不同，因为 INSTEAD OF 触发器代替触发动作进行激发。就拿同样的例子来说，如果在 Emplyees 表上有一个 INSTEAD OF UPDATE 触发器和在这个表上执行一条 UPDATE 语句，结果是这条 UPDATE 语句并不会改变 Employee 表中的任何一行。相反,这条 UPDATE 语句只有是为了踢离 INSTEAD OF UPDATE 触发器，这个触发器可能会，也可能不会改变 Employees 表中的数据。 　　因此，怎么决定在合适的时间和位置放置 INSTEAD OF 触发器呢？有几个关键的因素在做决定是值得考虑的。AFTER 触发器多用在动作必须在表中数据发生改变之后才执行后情情况。比如，AFTER 触发器可以用于将对数据作任何变动的日志记录在一个相对独立的审计表中。INTEAD OF 触发器也能做同样的工作。但是 INSTEAD OF 触发器在这个情况下的效率比较低，因为更新动作只能在将它发生的动作准确地记录在审计表之后才允许执行。 　　一般来说，只要不影响数据的修改，AFTER 触发器比 INSTEAD OF 触发器更有效率。在对数据进行计算或是对数据的修改作为一个整体提交或是作为一个整体回退的情况下，AFTER 触发器也是一个很好的选择。例如，存在这样一条规则：对在 Products 表的产品价格的变动超过30％的必须回退。AFTER 触发器能很漂亮地完成这个工作，它利用已插入同已删除的表中的产品价格作比较，然后在有必要的时回滚事务。这些都是 AFTER 触发器的理想条件，但有时 INSTEAD OF会更好些。 　　INSTEAD OF 触发器有一个很大的特点——就是它允许你在某个表或视图上用多个复杂的查询操作来代替单一的查询。跟 AFTER 触发器只能对表起作用不同，INSTEAD OF 触发器可以同时对表和视图起作用。我常常被问到怎么样去解决这种情况：有一个多表组成的视图，如何对该视图进行一次更新。如果视图包含有关键字段和包含有基本表的某些字段，这只是简单的更新基本表。但是，当有视图中包含有多个基本表示，逻辑上的更新比单单一个 UPDATE 语句会更复杂。因此，你是怎么利用什么可以替代的工具来解决这个问题的呢？其中一个方法就是将一个INSTEAD OF 触发器放在视图上。INSTEAD OF 触发器可以定义在一个或多个表上.INSTEAD OF 触发器就能转开在多个基本表中修改的范围. 　　例如，如果一个视图将 Customers、Products、orders 和 OrderDteils 等表合并成一个视图，并利用视图通过程序在屏幕上来显示所有的数据。更新操作便允许用来代替这个视图，假如存在一个这个样的视图：它包含 Northwind 数据库中的四个表，并且被命名为vwCustomersOrdersOrderDetailsProducts，它看起来像这样(Figure 1)：

Figure 1 连接 Customers 及其 Order Details 的视图 CREATE VIEW vwCustomersOrdersOrderDetailsProducts AS SELECT c.CustomerID, c.CompanyName, o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity, od.Discount, p.ProductID, p.ProductName FROM Customers c INNER JOIN Orders o ON c.CustomerID = o.CustomerID INNER JOIN [Order Details] od ON o.OrderID = od.OrderID INNER JOIN Products p ON od.ProductID = p.ProductID GO 　　vwCustomersOrdersOrderDetailsProducts 视图连接着四个表，并且每个表都暴露一个取样字段。必须记住的一点是，当你设计一个含有 INSTEAD OF UPDATE 的触发器时，将每个表的主关键字段包含在SELECT语句中是很有益的做法。即使这些字段在应用程序不会用到，它们也以在 INSTEAD OF 触发器中用来定位将要被修改的行，然后对基表作相应的修改。假设你打算允许更新该视图以便按非关键字过滤基表。更新代码应该写在 INSTEAD OF UPDATE 触发器中，让触发器去更新 Customers 表中的 CompnayName 列，Orders 表中的 OrderDate 列，Order Details 表的 UnitPrice 和 Quantity 列以及在 Products 表中的 ProductName 列。在这种情况下，使用 AFTER 触发器就不适合了，而 INSTEAD OF 触发器则是一个很好的选择，参见 Figure 2：

Figure 2 用 INSTEAD OF 触发器更新视图 CREATE TRIGGER tr_vwCustomersOrdersOrderDetailsProducts_IO_U ON vwCustomersOrdersOrderDetailsProducts INSTEAD OF UPDATE AS — 更新 Customers UPDATE Customers SET CompanyName = i.CompanyName FROM inserted i INNER JOIN Customers c ON i.CustomerID = c.CustomerID — 更新 Orders UPDATE Orders SET OrderDate = i.OrderDate FROM inserted i INNER JOIN Orders o ON i.OrderID = o.OrderID — 更新 Order Details UPDATE [Order Details] SET UnitPrice = i.UnitPrice, Quantity = i.Quantity FROM inserted i INNER JOIN [Order Details] od ON i.OrderID = od.OrderID AND i.ProductID = od.ProductID — 更新 Products UPDATE Products SET ProductName = i.ProductName FROM inserted i INNER JOIN Products p ON i.ProductID = p.ProductID GO 　　注意在 Figure 2 中的 INSTEAD OF UPDATE 触发器包含了四个 UPDATE 语句。每个 UPDATE语句目的都是为了对其中一个基表中的非关键字段进行修改。在 UPDATE 语句中包含了每个表中的关键字段对应于视图中的字段。这样就允许 UPDATE 语句在相应的表中定位对应的列并只对这些列作修改。下面的 UPDATE 语句将对 INSTEAD OF 触发器进行测试：

UPDATE vwCustomersOrdersOrderDetailsProducts SET Quantity = 100, UnitPrice = 20, CompanyName = ''''Fake Name'''', OrderDate = ''''11/23/2001'''', ProductName = ''''Widget'''' WHERE OrderID = 10265 AND ProductID = 17 　　 　　如果你（通过视图或是表自身）检查相应表中的值，很明显，这些值已被更新了。当然，对INSTEAD　OF 触发器作一些改变会使其有不同的结果。例如，不存在写一个触发器去改变四个基表的需求，因此，可以将触发器中的一个或是多个 UPDATE 语句删去。假设 INSTEAD　OF 触发器仅仅是为了更新 Order Details 表的值，这就会仅仅更新在 Order Details 表中的字段，而忽视任何在其他基表上的修改。在这种情况下，在 Customers,Products 或是 Orders 表中不会产生任何错误同时也不会发生任何改变。当然，如果这三个表中的某些字段发生改变的话，会发生报错。如我呆会在这篇文章会讨论的一样，UPDATE 和 COLUMNS＿UPDATED 函数是个检测哪些字段发生改变的理想的方法。 　　Figure 2 也演示了怎么写一个触发器修改多行记录。注意到 UPDATE 语句如何按关键字连接被插入的表和各个基表。这就保证更新是对所有的行，这些行在视图中被原有的 UPDATE 语句修改。通过循环被插入表的记录行也能完成该操作。不管怎么样，通常避免使用游标是个好主意，尤其是在使用触发器时更应如此。SQL SERVER 被设计成以数据集的方式来处理数据，而游标是为一次处理一个数据行而设计的。在触发器中使用游标会降低程序的性能，因此，最好能使用象 Figure 2 中那样更有效代替方法或使用一个子查询。 　　另一个改变 INSERT OF UPDATE 触发器的方法就是使其在视图的 INSERT 和 DELETE 语句中激发。这也就意味着在适当的地方，触发器会实现 INSERT 或是 DELETE 的功能。但是必须记隹的是 DELETE 可能会删除多个记录，这关键在于触发器是怎样写的。因此，检查触发器的需求，在实现之前进行测试，这些做法十分重要。INSERT　OF UPDATE