Two special tables are used in trigger statements: the deleted table and the inserted table. You can use these temporary tables to test the effects of certain data modifications and to set conditions for trigger actions. You cannot alter the data in the trigger test tables directly, but you can use the tables in SELECT statements to determine whether the trigger was fired by 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 deleted 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 UPDATE transaction, new rows are added simultaneously to both the inserted table and the trigger table. The rows in the inserted table are copies of the new rows in the trigger table.
An UPDATE transaction is like 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 you set trigger conditions, use the inserted and deleted tables appropriately for the action that fired the trigger. Although referencing deleted while testing an INSERT, or inserted while testing a DELETE does not cause any errors, these trigger test tables will not 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 effects, 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.
Microsoft® SQL Server™ does not allow text, ntext, or image column references in the inserted and deleted tables if the compatibility level is 70. For more information, see CREATE TRIGGER.