分类： C/C++
2008-04-23 21:44:46
探究 SQL Serverr 的触发器:第二部分
原著：
翻译：
原文出处：
下载源代码： (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 函数是个检测哪些字段发生改变的理想的方法。 更新分开的列 CREATE VIEW vwOrdersOrderDetailsProducts
AS
SELECT o.OrderID,
o.OrderDate,
od.UnitPrice * od.Quantity AS ExtendedPrice,
p.ProductID,
p.ProductName
FROM Orders o
INNER JOIN [Order Details] od ON o.OrderID = od.OrderID
INNER JOIN Products p ON od.ProductID = p.ProductID
GO
这个视图揭示了一个计算型的列叫ExtendedPrice，这个列不能被直接被更新，因为它不能将其自己变为表中独立的一列。虽然你可实现这样一个生意规则，在这个规则中ExtendedPrice通过这个视图来修改，Quantity列不应修改，但是UnitPrice可被修改(我知道这条规则有点奇怪，但我可以忍受这点)。可以写一个INSTEAD OF UPDATE触发器来增强这条生意规则，其代码如下所示：
CREATE TRIGGER tr_vwOrdersOrderDetailsProducts_IO_U
ON vwOrdersOrderDetailsProducts
INSTEAD OF UPDATE
AS
UPDATE [Order Details]
SET UnitPrice = i.ExtendedPrice / Quantity
FROM inserted i
INNER JOIN [Order Details] od ON i.OrderID = od.OrderID AND
i.ProductID = od.ProductID
GO
这些代码揭示了怎样用一个在INSTEAD OF 触发器中的逻辑来代替对一个计算型列的更新。假设一个产品在一张特定的定单表中Quantity为100而ExtendedPrice要更新为200，这时新的UnitPrice值就变为2。在这种情况下，在执行一个对ExtendedPrice列进行修改的UPDATE语句时，最终的结果是UnitPrice被赋为ExtendedPrice除以Quantity的商。下面的代码可以用来测试这种情况：
UPDATE vwOrdersOrderDetailsProducts SET ExtendedPrice = 200 WHERE OrderID = 10265 AND ProductID = 17
检查改变CREATE TRIGGER tr_Employees_U on Employees AFTER UPDATE AS
IF UPDATE(lastname)
BEGIN
RAISERROR (''''cannot change lastname'''', 16, 1)
ROLLBACK TRAN
RETURN
END
GO
UPDATE功能是为了判断单一列是否被INSERT或是UPDATE语句修改过。UPDATE(列)是一个用来检测更新的标准的方法。但是当需要用来他检测多列是否受到INSERT或UPDATE语句的影响时就变得更低效率。而这恰恰是COLUM_UPDATE功能的一个亮点。COLUMN_UPDATE功能返回一个位掩码来判断特定的列是否被修改过。位掩码是包含在被表中被修改的列中的一个比特，目的是为了在表模式中定义这些列。如果一行修改，这比特位的值就为1，否则为0。不像从右到左地读字节的常规方法，位掩码是从左往右读。例如，下面的代码提示了一个在Order Details表中的触发器，这个触发器是为了检测Quantity和UnitPrice二个字段是否被修改过。
CREATE TRIGGER tr_OrderDetails ON [Order Details] AFTER UPDATE
AS
IF (COLUMNS_UPDATED() = 12)
BEGIN
RAISERROR (''''Cannot change both UnitPrice and Quantity at the
same time'''', 16, 1)
ROLLBACK TRAN
END
GO
如果这个字段都被修改了，就会产生一个错误，同时事务也将回滚。就拿Order Details表来说，COLUMN_UPDATED功能返回代表Order Details表中字段的五个字节。只要第三和第四个字段被修改，上面这种情况就会发生，它检测这些位是不是已赋值为1.当第三和第四位都打开的庆，它就如：00110。L因这个位掩码代表2次幂，第一位表示1，第二位表示2，第三位表示4，第四位表示8，第五位表示16（是的，这是和正常二进制数相反的顺序）；因此只表示UnitPrice和Quantity字段被修改位掩码的值为00110，这个值为12（4 8）。请注意，这个触发器只有在UnitPrice和Quantity字段被修改才会将事务回滚。如果其他字段修改的话，位掩码就会不一样，因此就不等于整数12了。如果触发器被修改为禁止对这二个字段修改即使对其他字段也禁止，它就可重新编写为如下:
ALTER TRIGGER tr_OrderDetails ON [Order Details] AFTER UPDATE
AS
IF (COLUMNS_UPDATED() >= 12)
BEGIN
RAISERROR (''''Cannot change both UnitPrice and Quantity
at the same time'''', 16, 1)
ROLLBACK TRAN
END
GO
请注意 COLUMN_UPDATED 功能现在是怎么去检测位掩码的但是否小于等于12.如果你修改联系UnitPrice,Quantity和Discount列的话，位掩码就变为00111，代表整数28（4 8 16）。当在一个表中不止有8个列时，这个函数就会先返回包含了前八列的五个字节，而第从第九到第十六就会在第二个字节中，以此类推。这个功能在决定允许哪些列可以被更新比只对第列进行更新的UPDATE功能更有用。 结束语