An important consideration to keep in mind when writing the code for a trigger is that the statement that causes the trigger to fire can be a single statement that affects multiple rows of data, rather than a single row. This is common for UPDATE and DELETE triggers because these statements often affect multiple rows. It is less common for INSERT triggers because the basic INSERT statement adds only a single row. However, since an INSERT trigger can be fired by an INSERT INTO (table_name) SELECT statement, hundreds of rows can be affected by an INSERT trigger.
Multirow considerations are particularly important when the function of a trigger is to automatically recalculate summary values from one table and store the results in another for ongoing tallies.
The triggers in the following examples are designed to store a running total of a column in another table. The first version of the trigger works well for a single row insert, when a row of data is loaded into the sales table. The trigger is fired by an INSERT statement and the new row is loaded into the inserted table for the duration of the trigger execution. The UPDATE statement reads the qty column value for the row and adds it to the existing value in the ytd_sales column in the titles table. The WHERE clause ensures that the updated row in the sales table matches the title_id of the row in the inserted table.
-- Trigger valid for single row INSERTS.
CREATE TRIGGER intrig
ON sales
FOR INSERT AS
UPDATE titles
SET ytd_sales = ytd_sales + qty
FROM inserted
WHERE titles.title_id = inserted.title_id
In the case of a multirow INSERT, this trigger might not operate correctly; the expression to the right of an assignment expression in an UPDATE statement (ytd_sales + qty) can be only a single value, not a list of values. So the effect of the trigger is to obtain a value from any single row in the inserted table and add it to the existing ytd_sales value in the titles table for a given title_id value. This might not have the desired effect if a single title_id value occured more than once in the inserted table.
To update the titles table properly, the trigger has to accommodate the possibility of multiple rows in the inserted table. This can be done with the SUM function that calculates the total qty for a group of rows in the inserted table for each title_id. The SUM function is placed in a correlated subquery (the SELECT statement in parentheses), which returns a single value for each title_id in the inserted table that matches or is correlated with a title_id in the titles table.
-- Trigger valid for multirow and single row inserts.
CREATE TRIGGER intrig
ON sales
FOR INSERT AS
UPDATE titles
SET ytd_sales = ytd_sales +
(SELECT SUM(qty) -- Correlated subquery.
FROM inserted
WHERE titles.title_id = inserted.title_id)
WHERE titles.title_id IN
(SELECT title_id FROM inserted)
This trigger also works correctly in a single row insert; the sum of the qty value column is the sum of a single row. However, with this trigger the correlated subquery and the IN operator used in the WHERE clause require additional processing from Microsoft® SQL Server™, which is unnecessary for a single row insert. Therefore, 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.
CREATE TRIGGER intrig
ON sales
FOR INSERT AS
IF @@ROWCOUNT = 1
BEGIN
UPDATE titles
SET ytd_sales = ytd_sales + qty
FROM inserted
WHERE titles.title_id = inserted.title_id
END
ELSE
BEGIN
UPDATE titles
SET ytd_sales = ytd_sales +
(SELECT SUM(qty)
FROM inserted
WHERE titles.title_id = inserted.title_id)
WHERE titles.title_id IN
(SELECT title_id FROM inserted)
END