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

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

当 DML 触发器的功能是自动从一个表中重新计算汇总值并将结果存储到另一个表中以进行持续累计时，多行处理显得尤为重要。

例子

以下示例中的 DML 触发器旨在将运行的总列存储在 AdventureWorks2012 示例数据库的另一个表中。

答： 存储单行插入的累计总和

当将数据行加载到 PurchaseOrderDetail 表中时，DML 触发器的第一个版本适用于单行插入。 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赋值表达式右侧的表达式只能是单个值，不能是值列表。 因此，触发器的效果是从插入的表中的任何单行中检索值，并将该值添加到表中的现有SubTotal值以获取特定PurchaseOrderID值PurchaseOrderHeader。 如果单个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 列的值总和就是这一行的总和。 但是，使用此触发器时，相关子查询和 IN 子句中使用的 WHERE 运算符需要从 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 触发器