探究 SQL Serverr 的触发器

最新推荐文章于 2024-02-04 16:46:28 发布

Dreamfine

最新推荐文章于 2024-02-04 16:46:28 发布

阅读量2.7k

点赞数

分类专栏： Ajax 文章标签： sql server insert join delete go 测试

Ajax 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

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

原著：John Papa

翻译：Anlee

原文出处：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 触发器可写在视图中，因此它可插入一个新的顾客、订单、详细的订单和产品。这个触发器也可以用来在插入一个新顾客之前检查这个顾客是否是新的(对其它记录的操作也是一样)。当采用的是 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
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功能更有用。
　　如前期所描述的一样，在潢足特定条件规则条件下，触发器可以回滚事务，当一个含有回滚的触发器在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 的邮箱 mmdata@microsoft.com
　

作者简介：John Papa 是个棒球爱好者，他将夏季晚上的大部分时候花费在他的二个小女儿，妻子还由他忠诚的狗 Kadi 身上。他已经编写了几本关于ADO，XML 和 SQL Server 的书。可常常在象 VSLive 这样的行业会议上看到他。你可以通过 data@lancelotweb.com 和他联系。
　

本文出自 MSDN Magazine 的 January 2004 期刊，可通过当地报摊获得，或者最好是订阅