Triggers - Inserted and Deleted Virtual Tables

(Page 2 of 4 )

SQL Server maintains two temporary virtual tables during the execution of a trigger: Deleted and Inserted. These tables contain all the records inserted or deleted during the operation that fired the trigger. You can use this feature to perform additional verification or additional activities on affected records.

You are probably wondering if there is an Updated table. No. Because an Update can be performed as a combination of the Delete and Insert statements, records that were updated will appear in both the Deleted and Inserted tables.

SQL Server does not create both tables in all cases. For example, in a trigger fired during a Delete statement, only a Deleted virtual table is accessible. A reference to an Inserted virtual table will cause an error.

The following table summarizes the presence of virtual tables in the relevant Transact-SQL statements:

Modification Statement	Deleted	Inserted
Insert	N/A	New records
Update	Old version of updated records	New version of updated records
Delete	Deleted records	N/A

The following modifies the trigger from the previous section to display which records are deleted:

Alter Trigger trMyEquipment_D
On dbo.MyEquipment
After Delete     -- For Delete
As
     Select 'You have just deleted following '
          + Cast(@@rowcount as varchar)
          + ' record(s)!'

     Select * from deleted
go

When you delete all records from the MyEquipment table, SQL Server returns the following:

-------------------------------------------
You have just deleted following 5 record(s)!

(1 row(s) affected)

Id          Description
----------- -------------------------------1           Toshiba Portege 7020CT
2           Sony Trinitron 17XE
3           NEC V90
4           HP LaserJet 4
5           HP LaserJet 4

(5 row(s) affected)

You can use values from these tables, but you cannot modify them directly. If you need to perform some operation on records that were inserted, for example, you should not try to change them in the Inserted table. The proper method would be to issue a regular Transact-SQL statement against the original table. In the Where or From clause, you can reference the virtual table (Inserted) and in that way limit the subset of the original table that you are targeting.

In the following example, the trigger calculates a SOUNDEX code for the Make and Model of the Equipment records affected by the Insert or Update statement that has fired the trigger:

Alter Trigger trEquipment_IU
On dbo.Equipment
After Insert, Update   -- For Insert, Update
As
     -- precalculate ModelSDX and MakeSDX field
     -- to speed up use of SOUNDEX function
     update dbo.Equipment
     Set ModelSDX = SOUNDEX(Model),
          MakeSDX = SOUNDEX(Make)
     where EqId IN (Select EqId from Inserted)

Next: What Triggers a Trigger? >>
 

More MS SQL Server Articles
More By McGraw-Hill/Osborne

Comments On: Triggers

· This article is an excerpt from the book "Microsoft SQL Server 2005 Stored Procedure...    · nice article and exlent presentation.i am very thankful to you    · Glad you like it; there's more coming.    · Its Reaaly Useful And Nice Topic.Concepts Explained Very Clearly With Simple And...    · Great article. Very, very helpful.   >>> Post your comment now!

Buy this book...

This article is excerpted from chapter nine of the book Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL and .NET, written by Dejan Sunderic (McGraw-Hill/Osborne, 2006; ISBN: 0072262281). Check it out today at your favorite bookstore. Buy this book now.