创建 DML 触发器以处理多行数据

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

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

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

示例

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

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

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

-- Trigger is valid for single-row inserts.
USE AdventureWorks2012;
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 AdventureWorks2012;
GO
CREATE TRIGGER NewPODetail2
ON Purchasing.PurchaseOrderDetail
AFTER INSERT AS
   UPDATE Purchasing.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 AdventureWorks2012;
GO
CREATE TRIGGER NewPODetail3
ON Purchasing.PurchaseOrderDetail
FOR INSERT AS
IF @@ROWCOUNT = 1
BEGIN
   UPDATE Purchasing.PurchaseOrderHeader
   SET SubTotal = SubTotal + LineTotal
   FROM inserted
   WHERE PurchaseOrderHeader.PurchaseOrderID = inserted.PurchaseOrderID
END
ELSE
BEGIN
      UPDATE Purchasing.PurchaseOrderHeader
   SET SubTotal = SubTotal + 
      (SELECT SUM(LineTotal)
      FROM inserted
      WHERE PurchaseOrderHeader.PurchaseOrderID
       = inserted.PurchaseOrderID)
   WHERE PurchaseOrderHeader.PurchaseOrderID IN
      (SELECT PurchaseOrderID FROM inserted)
END;

请参阅

概念

DML 触发器