Triggers – Set Based, not Row-based

A common misconception is that triggers fire once per row, and there are a couple of examples in Books Online that don’t help.  For example, if you have a trigger on an update statement, and you call:

UPDATE Widgets SET [Status] = 1 WHERE Id IN (1, 3, 5, 10);

then you might expect to see the trigger fire once for every row that is being updated.

Not so.

Triggers fire once per statement, unless recursive triggers are enabled and your trigger updates a table which fires a trigger to update the original table.  This means, in the statement above, that up to four rows will be modified (assuming that Id is the primary key), and possibly less, if some of the values are missing.

When using triggers, two “magic” tables come into being, Inserted and Deleted. The Inserted table contains all rows that have been inserted into the table, and not surprisingly, the Deleted table holds those rows that have been deleted.  An UPDATE statement is considered to have deleted the old values, and inserted the new values, so a row will appear in both magic tables for an update statement.

In the UPDATE statement above, we can then expect to see one to four rows in both the inserted and updated table, depending on how many of the specified ID values existed (if none of the IDs existed, the trigger wouldn’t fire at all).  However, this code is quite common in triggers, and is fundamentally flawed if more than one row is updated:

CREATE TRIGGER TriggerOnUpdateWidgets ON Widgets
FOR UPDATE AS
    -- This is an incorrect way of using a trigger - assumes only one row will be modified!
    DECLARE @WidgetId int, @Name varchar(25), @Status int
    SELECT @WidgetId = Id, @Name = [Name], @Status = [Status]
    FROM deleted
    
    INSERT INTO WidgetsHistory (WidgetId, [Name], [Status])
    VALUES (@WidgetId, @Name, @Status) 
GO

The aim of this trigger is to write a row into the WidgetsHistory table when a row in Widgets is updated.  It will work fine as long as the update only ever operates on one row at a time.  It will also appear to work if multiple rows are updated, as the scalar variables @WidgetId, @Name, and @Status will be filled with values without a check to see how many rows are returned from Deleted.

The solution is to treat the trigger operation as a set, and insert all rows into the WidgetsHistory table:

CREATE TRIGGER TriggerOnUpdateWidgets ON Widgets
FOR UPDATE AS
    INSERT INTO WidgetsHistory (WidgetId, [Name], [Status])
    SELECT deleted.Id, deleted.[Name], deleted.[Status]
    FROM deleted
GO

The code is shorter, which is a side benefit.  If you need to do row-by-row processing, you might consider using a cursor inside your trigger, but I would recommend against this.  Triggers should be fast and lightweight, and the more complicated they are, the more likely they are to fail and rollback your transaction.  Additionally, triggers can be disabled, and so the logic provided by the trigger will not be run.  Instead, you might consider the trigger placing a request on a Service Broker queue so that it will run asynchronously, or redesign the application to handle this in a different manner.  Consider performing a check during the trigger to ensure that only one row is updated at a time, and all other transactions are rolled back.

Sample code to illustrate set based triggers:

CREATE DATABASE TriggerTestDB;
GO
USE TriggerTestDB;

-- Create a table of Widgets
CREATE TABLE Widgets (
      Id int not null identity primary key
    , [Name] varchar(25) not null
    , [Status] int not null)
GO

-- Create a table to record the historical values of the widgets
CREATE TABLE WidgetsHistory (
      HistoryDate datetime default CURRENT_TIMESTAMP
    , WidgetId int not null
    , [Name] varchar(25) not null
    , [Status] int not null
    , PRIMARY KEY (HistoryDate, WidgetId)
)
GO

-- Works fine
CREATE TRIGGER TriggerOnUpdateWidgets ON Widgets
FOR UPDATE AS
    INSERT INTO WidgetsHistory (WidgetId, [Name], [Status])
    SELECT deleted.Id, deleted.[Name], deleted.[Status]
    FROM deleted
GO

-- Populate the table with 50 widgets
SET NOCOUNT ON;
INSERT INTO Widgets ([Name], [Status]) VALUES ('WidgetName', 0);
GO 50

-- Update four of the widgets's status
UPDATE Widgets SET [Status] = 1 WHERE Id IN (1, 3, 5, 10);
GO

-- Have a look in the tables
SELECT * FROM Widgets;
SELECT * FROM WidgetsHistory;
GO

-- Cleanup
USE [Master];
DROP DATABASE TriggerTestDB;
GO