DML トリガの複数行に関する注意点

最初の例の DML トリガは、PurchaseOrderDetail テーブルに 1 行のデータが追加される場合、つまり 1 行単位の挿入は問題なく実行できます。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 ;

複数行を挿入する場合は、例 A で示した DML トリガは正しく実行されない可能性があります。UPDATE ステートメントの代入式の右側の式 (SubTotal + LineTotal) は必ず単一の値になり、値のリストを指定することはできません。したがって、このトリガの結果は、inserted テーブル内の 1 行から値を取得して、その値を、特定の PurchaseOrderID 値に対する PurchaseOrderHeader テーブルの既存の SubTotal 値に追加することになります。この操作では、1 つの PurchaseOrderID が inserted テーブルに複数登録されている場合には、予想どおりの結果が得られないことがあります。

PurchaseOrderHeader テーブルを正しく更新するには、inserted テーブルに複数の行がある場合を考慮してトリガを作成する必要があります。これを行うには、PurchaseOrderID ごとに inserted テーブル内の行グループで LineTotal の合計を計算する SUM 関数を使用します。SUM 関数は、相関サブクエリ (かっこ内の SELECT ステートメント) に指定します。このサブクエリは、PurchaseOrderHeader テーブルの PurchaseOrderID に一致するか、これと相関関係にある inserted テーブルの 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);

このトリガは 1 行だけの挿入でも正しく実行されます。この場合、LineTotal 値の列の合計は、1 つの行だけの値になります。ただし、このトリガでは、相関サブクエリと WHERE 句で使用されている IN 演算子のために、SQL Server による追加の処理を必要とします。これは、1 行単位の挿入操作の場合は必要ありません。

処理対象の行数に応じて最適の方法を使用するようにトリガを変更できます。たとえば、1 行だけの挿入と複数行の挿入を区別するには、トリガのロジックで @@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;