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