'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 = $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.


If 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.

Listing 29.2 Viewing the Contents of the inserted and deleted Tables
--Create a copy of the titles table in the Pubs database
 INTO titles_copy
 FROM titles
--add an AFTER trigger to this table for testing purposes
CREATE TRIGGER tc_tr ON titles_copy
 PRINT 'Inserted:'
 SELECT title_id, type, price FROM inserted
 PRINT 'Deleted:'
 SELECT title_id, type, price FROM deleted

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 = $15.05
 WHERE type LIKE '%cook%'

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

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
Statement Contents of inserted Contents of deleted
INSERT Rows added Empty
UPDATE New rows Old rows
DELETE Empty Rows deleted


Triggers 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.

    Part III: SQL Server Administration
    Part IV: Transact-SQL
    Part V: SQL Server Internals and Performance Tuning
    Part VI: Additional SQL Server Features