SQL Server Trigger Instead of Delete

By: Daniel Farina

Overview

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.

Additional Information

• Using INSTEAD OF triggers in SQL Server for DML operations