SQL Server Trigger Instead of Delete


By:
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.

Instead of Delete  trigge Deleting rows in the Person view.
Additional Information

Last Update: 8/2/2021




Comments For This Article





download














get free sql tips
agree to terms