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 = $15.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.
TIPIf you want to be able to see the contents of these tables for testing purposes, create a copy of the table, and then create a trigger on that copy (see Listing 29.2). You can perform data modification statements and view the contents of these tables without the modification actually taking place. |
--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.
UPDATE titles_copy SET price = $15.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.
Statement | Contents of inserted | Contents of deleted |
---|---|---|
INSERT | Rows added | Empty |
UPDATE | New rows | Old rows |
DELETE | Empty | Rows deleted |
NOTETriggers do not fire on a row-by-row basis. One common mistake is to assume that only one row is modified when coding your trigger. Triggers are set based. If a single statement affects multiple rows in the table, then the trigger will need to handle the processing of all of the rows that were affected, not just one row at a time. One common approach to dealing with the multiple rows in the trigger is to place the rows in a cursor and then process each row that was affected one at a time. This will work, but it can have an adverse affect on the performance of the trigger. Try to use rowset-based logic instead of cursors in your triggers when possible. This will keep your trigger execution fast. |