Testing Data Modifications Against the Trigger Test Tables

Two special tables are used in trigger statements: the deleted table and the inserted table. These temporary tables are used in trigger tests. Use these tables to test the effects of certain data modifications and to set conditions for trigger actions. You cannot directly alter the data in the trigger test tables, but you can use the tables in SELECT statements to detect the effects of an INSERT, UPDATE, or DELETE statement.

• The deleted table stores copies of the affected rows during DELETE and UPDATE statements. During the execution of a DELETE or UPDATE statement, rows are removed from the trigger table and transferred to the deleted table. The deleted table and the trigger table ordinarily have no rows in common.
• The inserted table stores copies of the affected rows during INSERT and UPDATE statements. During an INSERT or an UPDATE transaction, new rows are added to inserted and the trigger table at the same time. The rows in inserted are copies of the new rows in the trigger table.
• An UPDATE transaction is, conceptually, a delete followed by an insert; the old rows are copied to the deleted table first, and then the new rows are copied to the trigger table and to the inserted table.

When setting trigger conditions, use trigger test tables that are appropriate for modifying data. Although it is not wrong to reference deleted while testing an INSERT, or inserted while testing a DELETE, these trigger test tables won't contain any rows in these cases.

Note A given trigger fires only once per statement. If trigger actions depend on the number of rows a data modification affects, you should use tests (such as an examination of @@ROWCOUNT) for multirow data modifications (an INSERT, DELETE, or UPDATE based on a SELECT statement), and take appropriate actions.

The trigger examples in the following sections accommodate multirow data modifications as necessary. The @@ROWCOUNT variable, which stores the number of rows affected by the most recent data modification operation, tests for a multirow insert, delete, or update. If any other SELECT statement precedes the test on @@ROWCOUNT within the trigger, you should use local variables to store the value for later examination. (All Transact-SQL statements that don't return values reset @@ROWCOUNT to 0).