inserted and deleted Tables
In most trigger situations, you need to know what changes were made as part of the data modification. You can find this information in the inserted and deleted tables. For the AFTER trigger, these tables are actually views of the rows in the transaction log that were modified by the statement. With the new INSTEAD OF trigger, the inserted and deleted tables are actually temporary tables that are created on-the-fly .
The tables have identical column structures and names as the tables that were modified. Consider the following statement that you can run against the Pubs database:
UPDATE titles SET price = .05 WHERE type LIKE '%cook%'
When this statement is executed, a copy of the rows to be modified is recorded along with a copy of the rows after the modification. These copies are available to the trigger in the deleted and inserted tables.
Listing 29.2 Viewing the Contents of the inserted and deleted Tables
--Create a copy of the titles table in the Pubs database SELECT * INTO titles_copy FROM titles GO --add an AFTER trigger to this table for testing purposes CREATE TRIGGER tc_tr ON titles_copy FOR INSERT, UPDATE, DELETE AS PRINT 'Inserted:' SELECT title_id, type, price FROM inserted PRINT 'Deleted:' SELECT title_id, type, price FROM deleted ROLLBACK TRANSACTION
The inserted and deleted tables are available within the trigger after INSERT , UPDATE , and DELETE . In Listing 29.3, you can see the contents of inserted and deleted , as reported by the trigger when executing the preceding UPDATE statement.
Listing 29.3 Viewing the Contents of the inserted and deleted Tables When Updating the titles_copy Table
UPDATE titles_copy SET price = .05 WHERE type LIKE '%cook%' Inserted: title_id type price -------- ------------ --------------------- MC2222 mod_cook 15.0500 MC3021 mod_cook 15.0500 TC3218 trad_cook 15.0500 TC4203 trad_cook 15.0500 TC7777 trad_cook 15.0500 Deleted: title_id type price -------- ------------ --------------------- MC2222 mod_cook 19.9900 MC3021 mod_cook 2.9900 TC3218 trad_cook 20.9500 TC4203 trad_cook 11.9500 TC7777 trad_cook 14.9900
When a trigger executes after more than one data modification statement ( INSERT , UPDATE , or DELETE ), you can identify which statement initiated the trigger by examining the contents of the inserted and deleted tables, as shown in Table 29.1.
Table 29.1. Determine the Action That Fired the Trigger