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)