inserted and deleted Tables


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.

TIP

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

NOTE

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.



Microsoft SQL Server 2000 Unleashed
Microsoft SQL Server 2000 Unleashed (2nd Edition)
ISBN: 0672324679
EAN: 2147483647
Year: 2002
Pages: 503

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net