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.
Explanation
The DELETED Table
When we work with triggers on UPDATE and DELETE statements, we need to be aware of what the affected rows are 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 at 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
Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning. He studied at Universidad de Buenos Aires. Daniel started working as a programmer at a young age. Over the years he specialized in databases, particularly SQL Server and Oracle. Now with 30 years of age, his work experience includes working with various technologies like VB, C, .NET, web development, Windows and Linux systems. He likes to read about science, psychology, philosophy and many other things. In his spare time, he trains powerlifting aiming to compete.
- MSSQLTips Awards: Author of the Year – 2018 | Champion (100+ tips) – 2018 | Author Contender – 2015-2017, 2019
