In this article I will explain with simple example, how to write an Insert Of Delete Trigger in SQL Server.
This tutorial is applicable for all versions of SQL Server i.e. 2005, 2008, 2012, 2014, etc.
 
Database
I have made use of the following table Customers with the schema as follows.
SQL Server: Instead Of Delete Trigger Example
 
I have already inserted few records in the table.
SQL Server: Instead Of Delete Trigger Example
 
Below is the CustomerLogs table which will be used to log the Trigger actions.
SQL Server: Instead Of Delete Trigger Example
 
Note: You can download the database table SQL by clicking the download link below.
          Download SQL file
 
 
Instead Of Delete Triggers
Instead Of triggers are executed instead of any of the Insert, Update or Delete operations.
For example consider an Instead of Trigger for Delete operation, whenever a Delete is performed the Trigger will be executed first and if the Trigger deletes record then only the record will be deleted.
Below is an example of an Instead Of Delete Trigger. Whenever anyone tries to delete a row from the Customers table the following trigger is executed.
Inside the Trigger, I have added a condition that if record has CustomerId value 2 then such a record must not be deleted and an error must be raised. Also a record is inserted in the CustomerLogs table.
If the CustomerId value is not 2 then a delete query is executed which deletes the record permanently and a record is inserted in the CustomerLogs table.
CREATE TRIGGER [dbo].[Customer_InsteadOfDELETE]
       ON [dbo].[Customers]
INSTEAD OF DELETE
AS
BEGIN
       SET NOCOUNT ON;
 
       DECLARE @CustomerId INT
 
       SELECT @CustomerId = DELETED.CustomerId       
       FROM DELETED
 
       IF @CustomerId = 2
       BEGIN
              RAISERROR('Mudassar Khan''s record cannot be deleted',16 ,1)
              ROLLBACK
              INSERT INTO CustomerLogs
              VALUES(@CustomerId, 'Record cannot be deleted.')
       END
       ELSE
       BEGIN
              DELETE FROM Customers
              WHERE CustomerId = @CustomerId
 
              INSERT INTO CustomerLogs
              VALUES(@CustomerId, 'Instead Of Delete')
       END
END
 
The following error message shown when record with CustomerId 2 is deleted.
SQL Server: Instead Of Delete Trigger Example
 
The following screenshot displays the Log table after the Instead Of Trigger is executed.
SQL Server: Instead Of Delete Trigger Example