Multirow Considerations

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, because an INSERT trigger can be fired by an INSERT INTO (table_name) SELECT statement, the insertion of many rows may result in a single trigger invocation.

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.

Note  The use of cursors in triggers is not recommended because of the potentially negative impact on performance. Use rowset-based logic rather than cursors to design a trigger that affects multiple rows.

Examples

The triggers in the following examples are designed to store a running total of a column in another table.

A. Store a running total for a single-row insert

The first version of the trigger works well for a single-row insert, when a row of data is loaded into the sales table. An INSERT statement fires the trigger, 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 is valid for single-row inserts.
CREATE TRIGGER intrig
ON sales
AFTER INSERT AS

   UPDATE titles
   SET ytd_sales = ytd_sales + qty
   FROM inserted
   WHERE titles.title_id = inserted.title_id

B. Store a running total for a multirow or single row insert

In the case of a multirow insert, the trigger in Example A 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 occurred 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 is valid for multirow and single-row inserts.
CREATE TRIGGER intrig
ON sales
AFTER 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™ 2000, which is unnecessary for a single-row insert.

C. Store 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.
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