DML 触发器的多行注意事项

为 DML 触发器编写代码时,请考虑导致触发器激发的语句可能是影响多行数据(而不是单行)的单个语句。这对于 UPDATE 和 DELETE 触发器很常见,因为这些语句经常影响多行。而这对于 INSERT 触发器比较少见,因为基本 INSERT 语句仅添加单行。但是,由于 INSERT 触发器可以通过 INSERT INTO (table_name) SELECT 语句激发,所以插入许多行可能导致调用单个触发器。

在下列情况下关于多行的注意事项尤为重要:DML 触发器的功能自动重新计算一个表中的汇总值,并将结果存储在另一个表中以继续进行计数。

注意注意

我们建议不要在触发器中使用游标,因为它们可能会降低性能。若要设计一个影响多行的触发器,请使用基于行集的逻辑,而不要使用游标。

示例

下列示例中的 DML 触发器用于在 AdventureWorks2008R2 示例数据库的另一个表中存储某列的运行总计。

A. 存储单行插入的运行总计

第一种 DML 触发器在一行数据加载到 PurchaseOrderDetail 表中时适合于单行插入。INSERT 语句激发 DML 触发器,新行在触发器执行期间加载到插入的表中。UPDATE 语句读取该行的 LineTotal 列值,并将该值与 PurchaseOrderHeader 表的 SubTotal 列中的现有值相加。WHERE 子句确保 PurchaseOrderDetail 表中的更新行与插入的表中 PurchaseOrderID 行相匹配。

-- Trigger is valid for single-row inserts.
USE AdventureWorks2008R2;
GO
CREATE TRIGGER NewPODetail
ON Purchasing.PurchaseOrderDetail
AFTER INSERT AS
   UPDATE PurchaseOrderHeader
   SET SubTotal = SubTotal + LineTotal
   FROM inserted
   WHERE PurchaseOrderHeader.PurchaseOrderID = inserted.PurchaseOrderID ;

B. 存储多行或单行插入的运行总计

对于多行插入,示例 A 中的 DML 触发器可能不会正确运行;位于 UPDATE 语句 (SubTotal + LineTotal) 中赋值表达式右侧的表达式只能是一个值,而不能是一个值列表。因此,该触发器的作用是检索插入的表中任意一行的值,并将该值与 PurchaseOrderHeader 表中的现有 SubTotal 值相加,以获得特定 PurchaseOrderID 值。如果某个 PurchaseOrderID 值在插入的表中出现多次,则此操作可能无法达到预期效果。

若要正确更新 PurchaseOrderHeader 表,必须允许对插入的表中的多行使用触发器。可以通过使用 SUM 函数达到此目的,该函数计算每个 PurchaseOrderID 的插入的表中许多行的总 LineTotal。SUM 函数包含在相关子查询(括号中的 SELECT 语句)中。此子查询将为插入的表中的每个 PurchaseOrderID 返回一个值,该值与 PurchaseOrderHeader 表中的 PurchaseOrderID 匹配或相关。

-- Trigger is valid for multirow and single-row inserts.
USE AdventureWorks2008R2;
GO
CREATE TRIGGER NewPODetail2
ON Purchasing.PurchaseOrderDetail
AFTER INSERT AS
   UPDATE PurchaseOrderHeader
   SET SubTotal = SubTotal + 
      (SELECT SUM(LineTotal)
      FROM inserted
      WHERE PurchaseOrderHeader.PurchaseOrderID
       = inserted.PurchaseOrderID)
   WHERE PurchaseOrderHeader.PurchaseOrderID IN
      (SELECT PurchaseOrderID FROM inserted);

此触发器还适合于单行插入;LineTotal 值列的和为单行的和。但是,对于此触发器,相关子查询和 WHERE 子句中使用的 IN 运算符需要从 SQL Server 中进行其他处理。这对于单行插入来说,是不必要的。

C. 基于插入类型存储运行总计

可以更改触发器以针对不同行数使用最优方法。例如,可以在触发器逻辑中使用 @@ROWCOUNT 函数来区分单行插入和多行插入。

-- Trigger valid for multirow and single row inserts
-- and optimal for single row inserts.
USE AdventureWorks2008R2;
GO
CREATE TRIGGER NewPODetail3
ON Purchasing.PurchaseOrderDetail
FOR INSERT AS
IF @@ROWCOUNT = 1
BEGIN
   UPDATE PurchaseOrderHeader
   SET SubTotal = SubTotal + LineTotal
   FROM inserted
   WHERE PurchaseOrderHeader.PurchaseOrderID = inserted.PurchaseOrderID

END
ELSE
BEGIN
      UPDATE PurchaseOrderHeader
   SET SubTotal = SubTotal + 
      (SELECT SUM(LineTotal)
      FROM inserted
      WHERE PurchaseOrderHeader.PurchaseOrderID
       = inserted.PurchaseOrderID)
   WHERE PurchaseOrderHeader.PurchaseOrderID IN
      (SELECT PurchaseOrderID FROM inserted)
END;

请参阅

概念