SQL Server Triggers DELETED Table

By: Daniel Farina

Overview

In the previous chapter of this tutorial we learned how to use the INSERTED pseudo table in a trigger. Now in this chapter I will show you how the DELETED pseudo table can be used with both UPDATE and DELETE statements.

The DELETED Table

When we work with triggers on UPDATE and DELETE statements, we need to be aware of what are the affected rows for those events. The DELETED pseudo table has a copy of the rows that will be either updated or deleted. A word of advice, you have to think about UPDATE statements as a DELETE followed by an INSERT.

Continuing with the example of the previous chapter, take a look to the following code which is a trigger that deals with UPDATE statements.

CREATE TRIGGER TR_U_Test_PseudoTables
ON dbo.Test_PseudoTables
FOR UPDATE
AS
 
SELECT D.ItemID ,
       D.ItemText 
FROM Deleted D;
 
SELECT I.ItemID ,
       I.ItemText
FROM   Inserted I;
 
GO

This trigger makes a select to both deleted and inserted pseudo tables. Let's see what happens when we update a row on the Test_PseudoTables table.

SELECT * FROM dbo.Test_PseudoTables;
 
UPDATE dbo.Test_PseudoTables 
SET ItemText = 'Good morning!'
WHERE ItemID = 1;
 
SELECT * FROM dbo.Test_PseudoTables;

As you can see in the image below, when the update statement is executed it returns two sets of data. The first is a copy of the deleted pseudo table which contains the row that will be changed and the other is a copy of the inserted table with the new data.

Additional Information

• Understanding SQL Server inserted and deleted tables for DML triggers