Multirow Considerations for DML Triggers
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 AdventureWorks 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 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. 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 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);
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 2005. 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 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;