分类: 数据库开发技术
2008-03-14 12:24:12
这个月我会继续上个月的专栏——“SQL SERVER触发器的基础知识及其特点” 。我开这个专栏的目的是为了回应我收到的许多关于触发器的问题。我会从 INSTEAD OF 触发器以及它与AFTER 触发器差别开始讲起。然后我会在讨论一些使用 INSTEAD OF 触发器的情形。其中包括允许几个视图对多个表进行更新。最后,我会讨论触发器怎样去处理事务,编写改变某个表中多行记录的触发器,以及确定在触发器中哪些列被修改。 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 GOvwCustomersOrdersOrderDetailsProducts 视图连接着四个表,并且每个表都暴露一个取样字段。必须记住的一点是,当你设计一个含有 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 触发器可写在视图中,因此它可插入一个新的顾客、订单、详细的订单和产品。这个触发器也可以用来在插入一个新顾客之前检查这个顾客是否是新的(对其它记录的操作也是一样)。当采用的是 INSTEAD OF 触发器时存在有许多机会,但是,当然,触发器是为解决相应的需求这才是它的本质。 通常,当引用一张表的 UPDATE 语句试图去赋值一个计算型的,恒等型的或是时间戳型的列时,会产生一个错误,因为这些列的值必须是由SQL SERVER来决定的。这些列必须被包含在UPDATE 语句中以便能满足列不能为空的要求。但是,如果 UPDATE 语句用 INSTEAD OF 触发器引用一个视图,定义在触发器中的逻辑可以旁路掉这些列来避免错误的发生。为了达到这个目的,触发器决不能尝试去更新基表中相应列的值(让它们远离 UPDATE 语句的 SET 从句)。当某一条被处理的记录来自被插入的表时,计算型的,恒等型的或是时间戳型的列可以用一个虚假值以满足不为空值的要求,这时,INSTEAD OF 触发器将忽略这些值,正确的值由 SQL SERVER 设置。 更新分开的列 INSTEAD OF 触发器也很普遍地用于更新基表中计算型的列。例如,假设存在有如下这样一个叫 vwOrdersOrderDetailsProducts 的视图: 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检查改变 在INSTEAD OF和AFTER触发器中都有UPDATE和COLUMNS_UPDATE功能,这二种功能允许由触发器决定哪些字段由触发器的语句来改变。例如,下面的触发器阻止任何对Employees表中的lastname字段进行修改。在这里,UPDATE功能用来决定对哪些对字段的修改可以执行。如果超出发生了改变(而又是不允许修改的)就会产生一个错误。PAISERR OR功能和事务就会回退,回退会撤消所做的任何修改。UPDATE功能都可以在AGTER触发器和INSTEAD OF触发器中工作,而不是在外部工作。 CREATE TRIGGER tr_Employees_U on Employees AFTER UPDATE AS IF UPDATE(lastname) BEGIN RAISERROR (''''cannot change lastname'''', 16, 1) ROLLBACK TRAN RETURN END GOUPDATE功能是为了判断单一列是否被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功能更有用。 如前期所描述的一样,在潢足特定条件规则条件下,触发器可以回滚事务,当一个含有回滚的触发器在SQL脚本中执行时,整个处理将被取消。因此,被触发动作修改的的所有数据将由ROLLBACK TRANSACION语句回滚。虽然一个回滚并不阻止触发执行SQL语句所有在ROLLBACK TRANSACION语句后面的语句都会被执行 。特别是当一个触发器继续执行回滚语句后面的语句时,在回滚以后所作的任何修改都不会回滚。发生这种情况是因为当在触发器中执行了一个ROLLBACK TRANSACTION时,所以有的事务都被取消。因此当一个新的查询语句被执行时,一个新的不同与以前事务的事务就重新开始。因此,一般情况下,建议不要在ROLLBACK TRANSACTION语句后放置任何语句。 像回滚不会自动退出触发器一样,它也不会自动产生错误。如果必须回滚且必会产生错误,PAISERR OR语句应该放在退出触发器代码前,紧跟在回滚后. 结束语 在对同一个表的数据所作的修改会激发同样的INSTEAD OF触发器,这种触发器不会递归调用。因此,如果在Emplyee表中有一个INSTEAD OF触发器,P这个触发器是用来更新Employee表的,这并不会发生调用同一个INSTEAD OF触发器。如果允许这种递旭的话,更新应该被禁止。INSTEAD OF触发器和AFTER触发器的另一个不同在于Text,Ntext和Image列可以出现在被更新和删除的表的触发器中。这些二进制列会以如VARCAHAR数值出现在更新和删除表的触发器中,这种是可行的,但这并不是他们原始的数据类型。 这有一个有用的存储过程-sp_helptrigger 系统存储过程-来检测触发器。他返回定义在表上的触发器类型,这个表是传递给存储过程的。用这种方法,你可以看到哪些触发器和某个表有关联,什么操作动触发这些触发器和判断触发器是AFTER触发器还是INSTEAD OF触发器。 在最后二栏中,我已经讨论了AFTER触发器和INSTEAD OF触发器的多个方面。当然,还有许多情形下他们很有用,还有许多使用时机也没有提出来。当一个触发器必须查询其他表的情况下,触发器就会没有什么效率了。在这些情况下,触发器的性能和触发动作会受到很大损害。当使用得好时,触发器是一个很棒的工具,但是必须保证在使用他们之前必须对你的程序作一个全面的测试。 发送你的问题和评论到 John 的邮箱 |
作者简介:John Papa 是个棒球爱好者,他将夏季晚上的大部分时候花费在他的二个小女儿,妻子还由他忠诚的狗 Kadi 身上。他已经编写了几本关于ADO,XML 和 SQL Server 的书。可常常在象 VSLive 这样的行业会议上看到他。你可以通过 和他联系。 |
本文出自 的 期刊,可通过当地 报摊获得,或者最好是 |