Multirow Considerations

Multirow considerations are particularly important when the function of a trigger is to automatically recalculate summary values (ongoing tallies).

Note Triggers used to maintain summary values should contain GROUP BY clauses to create summary values when more than one row is being inserted, updated, or deleted. Because a GROUP BY clause imposes extra overhead, the following examples are written to test whether the value of @@ROWCOUNT is equal to one, meaning that only one row in the trigger table was affected. If @@ROWCOUNT is equal to one, the trigger actions take effect without a GROUP BY clause.

This insert trigger updates the ytd_sales column in the titles table every time a new sales row is added. It goes into effect whenever you record a sale by adding a row to the sales table. It updates the ytd_sales column in the titles table so that ytd_sales is equal to its previous value plus the value added to sales.qty. This keeps the totals up to date for inserts into sales.qty.

CREATE TRIGGER intrig
ON sales
FOR INSERT AS
    /* check value of @@rowcount */
IF @@rowcount = 1
    UPDATE titles
    SET ytd_sales = ytd_sales  qty
    FROM inserted
    WHERE titles.title_id = inserted.title_id
ELSE
    /* when rowcount is greater than 1,
    **use a GROUP BY clause */
    UPDATE titles
    SET ytd_sales = ytd_sales  (SELECT SUM(qty)
        FROM inserted
        GROUP BY inserted.title_id
        HAVING titles.title_id = inserted.title_id)

The following example shows a delete trigger that updates the ytd_sales column in the titles table every time one or more sales rows are deleted. It goes into effect whenever a row is deleted from the sales table. It updates the ytd_sales column in the titles table so that ytd_sales is equal to its previous value minus the value subtracted from sales.qty.

CREATE TRIGGER deltrig
ON sales
FOR DELETE
as
    /* check value of @@rowcount */
IF @@rowcount = 1
    UPDATE titles
    SET ytd_sales = ytd_sales - qty
    FROM deleted
    WHERE titles.title_id = deleted.title_id
ELSE
    /* when rowcount is greater than 1,
    **use a GROUP BY clause */
    UPDATE titles
    SET ytd_sales = ytd_sales - (SELECT SUM(qty)
        FROM deleted
        GROUP BY deleted.title_id
        HAVING titles.title_id = deleted.title_id)