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.
