建立 DML 觸發程序以處理多重資料列

適用於：SQL ServerAzure SQL 資料庫Azure SQL 受控執行個體

撰寫 DML 觸發程序的程式碼時，請考慮引發觸發程序的陳述式可能是影響多行資料的單一陳述式，而不只是影響一行資料。 此行為對 UPDATE 與 DELETE 觸發程序是常見的，因為這些陳述式經常會影響多個資料列。 然而該行為對 INSERT 觸發程序較不常見，因為基本 INSERT 陳述式僅會加入單一資料列。 不過，由於 INSERT 觸發程序可以由 INSERT INTO (table_name) SELECT 陳述式引發，所以插入多個資料列也許會造成單一觸發程序引動過程。

當 DML 觸發器執行自動重新計算從一個資料表的摘要值，並將結果存到另一個資料表中進行持續累計時，考慮多重資料列所造成的影響就顯得特別重要。

注意

我們不建議在觸發器中使用游標，因為這樣可能會降低效能。 若要設計出可影響多個資料列的觸發程序，請使用資料列集邏輯而非資料指標。

範例

在以下範例中，DML 觸發程序是用來將資料行的累計值儲存到 AdventureWorks2022 範例資料庫中的另一個資料表。

A. 儲存單列插入的累計值

當資料列載入至 PurchaseOrderDetail 資料表時，第一版 DML 觸發器在單一資料列插入時運作良好。 INSERT 陳述式引發 DML 觸發程序，而且在此觸發程序的執行期間，會將新的資料列載入到 inserted 資料表。 UPDATE 陳述式讀取列中的 LineTotal 欄位值，並將該值與 PurchaseOrderHeader 資料表中 SubTotal 欄位的現有值進行相加。 WHERE 子句確保 PurchaseOrderDetail 資料表中已更新的資料列與 PurchaseOrderID 資料表中 "inserted" 資料列的相符。

-- Trigger is valid for single-row inserts.  
USE AdventureWorks2022;  
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) 只能是單一數值，而不能是值的列表。 因此，觸發程序的作用是擷取 inserted 資料表中任意單一資料列中的值，並針對特定 SubTotal 值，將擷取的值與 PurchaseOrderHeader 資料表中現有的 PurchaseOrderID 值加以累計。 如果單一的 PurchaseOrderID 值在插入的 資料表中出現多次，那麼此操作可能不會產生預期的效果。

若要正確更新 PurchaseOrderHeader 資料表，觸發程序必須允許 inserted 資料表中可以有多個資料列。 只要使用 SUM 函數，針對每個 LineTotal ，計算出 inserted 資料表中一組資料列的 PurchaseOrderID總數，就可以完成這個目標。 SUM 函數包含於相互關聯的子查詢中 (括號內的 SELECT 陳述式)。 如果 PurchaseOrderID inserted 資料表中每個 與 PurchaseOrderID 資料表中的 PurchaseOrderHeader 相符或相互關聯，則此子查詢會為每個相符的項目傳回單一值。

-- Trigger is valid for multirow and single-row inserts.  
USE AdventureWorks2022;  
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 AdventureWorks2022;  
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 觸發程序