Adaptive Server uses two special tables in trigger statements: the deleted table and the inserted table. These are temporary tables used in trigger tests. When you write triggers, you can use these tables to test the effects of a data modification 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.
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 and trigger tables 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, new rows are added to the inserted and trigger tables at the same time. The rows in inserted are copies of the new rows in the trigger table. The following trigger fragment uses the inserted table to test for changes to the titles table title_id column:
if (select count(*) from titles, inserted where titles.title_id = inserted.title_id) != @@rowcount
Both inserted and deleted tables appear as views in
the transaction log, but they are fake tables in syslogs.
An update is, effectively, a delete followed
by an insert; the old rows are copied to the deleted table
first; then the new rows are copied to the trigger table and to
the inserted table. The following illustration
shows the condition of the trigger test tables during an insert,
a delete, and an update:
When setting trigger conditions, use the trigger test tables that are appropriate for the data modification. It is not an error to reference deleted while testing an insert or inserted while testing a delete; however, those trigger test tables do not contain any rows.
A given trigger fires only once per query. If trigger
actions depend on the number of rows affected by a data modification,
use tests, such as an examination of @@rowcount for
multirow data modifications, and take appropriate actions.
The following trigger examples accommodate multirow data modifications where 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, use local variables to store the value for later examination. All Transact-SQL statements that do not return values reset @@rowcount to 0.
