SQL Server Trigger Instead of Delete
By: Daniel Farina
This chapter of the triggers tutorial will give an example of an instead of delete trigger.
Sample Test Scenario
We are continuing with the scenario of the previous chapter, which consisted of two tables named Customers and Providers and a view that joins both tables named Person. As you will see in the code below, the trigger consists of two delete statements, one on each table and joined with the deleted pseudo table.
CREATE TRIGGER TR_D_Person ON dbo.Person INSTEAD OF DELETE AS DELETE Customers FROM dbo.Customers C INNER JOIN Deleted D ON C.CustomerCode = D.PersonCode; DELETE Providers FROM dbo.Providers P INNER JOIN Deleted D ON P.ProviderCode = D.PersonCode; GO
In order to test the trigger let's delete two rows.
SELECT * FROM dbo.Person; GO DELETE FROM dbo.Person WHERE PersonCode = 'C2' DELETE FROM dbo.Person WHERE PersonCode = 'P2' GO SELECT * FROM dbo.Person;
The next screen capture shows the execution of this test.