DML 觸發程序的多重資料列考量

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

當 DML 觸發程序執行自動重新計算來自資料表的摘要值，並將計算結果存至另一資料表的功能時，考慮多重資料列所造成的影響就顯得特別地重要。

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

範例

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

A. 儲存單一資料列插入的累加值

當一個資料列載入至 PurchaseOrderDetail 資料表時，第一種 DML 觸發程序版本非常適用於單一資料列插入。INSERT 陳述式引發 DML 觸發程序，而且在此觸發程序的執行期間，會將新的資料列載入到 inserted 資料表。UPDATE 陳述式為資料列讀取 LineTotal 資料行的值，並將此值與 PurchaseOrderHeader 資料表中 SubTotal 資料行中的現有值加以累計。WHERE 子句確定 PurchaseOrderDetail 資料表中已更新的資料列與 inserted 資料表中的 PurchaseOrderID 資料列相符。

-- Trigger is valid for single-row inserts.
USE AdventureWorks;
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 資料表中任意單一資料列中的值，並針對特定的 PurchaseOrderID 值，將擷取的值與 PurchaseOrderHeader 資料表中現有的 SubTotal 值加以累計。如果 inserted 資料表中出現多次 PurchaseOrderID 值，則此計算結果可能不是我們所要的。

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

-- Trigger is valid for multirow and single-row inserts.
USE AdventureWorks;
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 2005 進行額外的處理。這個動作對單一資料列插入是不必要的。

C. 根據插入類型儲存累加值

您可以變更觸發程序，使用最適於多個資料列的方法。例如，@@ROWCOUNT 函數可以在觸發程序邏輯中使用，以區別單一資料列插入及多資料列插入。

-- Trigger valid for multirow and single row inserts
-- and optimal for single row inserts.
USE AdventureWorks;
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;

請參閱

概念

實作 DML 觸發程序

說明及資訊

取得 SQL Server 2005 協助