Create DML Triggers to Handle Multiple Rows of Data

When you write the code for a DML trigger, consider that the statement that causes the trigger to fire can be a single statement that affects multiple rows of data, instead of a single row. This behavior is common for UPDATE and DELETE triggers because these statements frequently affect multiple rows. The behavior is less common for INSERT triggers because the basic INSERT statement adds only a single row. However, because an INSERT trigger can be fired by an INSERT INTO (table_name) SELECT statement, the insertion of many rows may cause a single trigger invocation.

Multirow considerations are especially important when the function of a DML trigger is to automatically recalculate summary values from one table and store the results in another for ongoing tallies.

Note

We do not recommend using cursors in triggers because they could potentially reduce performance. To design a trigger that affects multiple rows, use rowset-based logic instead of cursors.

Examples

The DML triggers in the following examples are designed to store a running total of a column in another table of the AdventureWorks2012 sample database.

A. Storing a running total for a single-row insert

The first version of the DML trigger works well for a single-row insert when a row of data is loaded into the PurchaseOrderDetail table. An INSERT statement fires the DML trigger, and the new row is loaded into the inserted table for the duration of the trigger execution. The UPDATE statement reads the LineTotal column value for the row and adds that value to the existing value in the SubTotal column in the PurchaseOrderHeader table. The WHERE clause makes sure that the updated row in the PurchaseOrderDetail table matches the PurchaseOrderID of the row in the inserted table.

-- 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. Storing a running total for a multirow or single-row insert

For a multirow insert, the DML trigger in example A might not operate correctly; the expression to the right of an assignment expression in an UPDATE statement (SubTotal + LineTotal) can be only a single value, not a list of values. Therefore, the effect of the trigger is to retrieve a value from any single row in the inserted table and add that value to the existing SubTotal value in the PurchaseOrderHeader table for a specific PurchaseOrderID value. This operation might not have the expected effect if a single PurchaseOrderID value occurred more than one time in the inserted table.

To correctly update the PurchaseOrderHeader table, the trigger must allow for the chance of multiple rows in the inserted table. You can do this by using the SUM function that calculates the total LineTotal for a group of rows in the inserted table for each PurchaseOrderID. The SUM function is included in a correlated subquery (the SELECT statement in parentheses). This subquery returns a single value for each PurchaseOrderID in the inserted table that matches or is correlated with a PurchaseOrderID in the PurchaseOrderHeader table.

-- 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);  

This trigger also works correctly in a single-row insert; the sum of the LineTotal value column is the sum of a single row. However, with this trigger the correlated subquery and the IN operator that is used in the WHERE clause require additional processing from SQL Server. This is unnecessary for a single-row insert.

C. Storing a running total based on the type of insert

You can change the trigger to use the method optimal for the number of rows. For example, the @@ROWCOUNT function can be used in the logic of the trigger to distinguish between a single and a multirow insert.

-- 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;  

See Also

DML Triggers