Instead Of DELETE Triggers in SQL Server

The SQL INSTEAD OF DELETE trigger is fired before the execution starts. So, you can use this trigger to store the values in another table before the delete option, or perform some operation, or deleting different table etc. In this article, we will show you, How to write the INSTEAD OF DELETE Triggers in SQL Server with an example.

For this demonstration, We are going to use the tables that we have shown below. As you can see from the below screenshot that our Employee table holds 14. Here, our task is to create an INSTEAD OF DELETE TRIGGER on this Employee table. And by using this trigger, we want to restrict the delete operation

TIP: You can refer INSTEAD OF INSERT Triggers in SQL Server article to understand the INSTEAD OF INSERT Trigger, and INSTEAD OF UPDATE Triggers in SQL Server article for INSTEAD OF UPDATE Trigger.

Employee Table Audit also holds the same 14 records, along with the Server Instance name, Server name, and Insert Time (Audit Information).

NOTE: SQL Instead of Delete Triggers can be created in Tables and Views. In general, we use these triggers on Views

Instead Of DELETE Triggers in SQL Server Example

In this example, we will show you, How to create an Instead of delete Triggers in SQL Server. For instance, if you want to restrict the users from deleting the records in the original table, and you want those logs in another historical table then you can use this trigger.

Here, we will create a trigger on the Employee table using the CREATE TRIGGER Statement. From the below code snippet you can see that we are using the MERGE Statement to select all the records that you want to delete from the Employee table, and then We are deleting those records from the Audit table.

It means when the user deletes any Employee table record then trigger will delete those records from the audit table, and kept the Employee table unchanged.

-- Example for INSTEAD OF DELETE Triggers in SQL Server
USE [SQL Tutorial]
GO
CREATE TRIGGER InsteadOfDELETETriggerExample on [EmployeeTable]
INSTEAD OF DELETE
AS 

MERGE [EmployeeTableAudit] AS AuditTab
USING (SELECT * FROM DELETED) AS Emp
ON AuditTab.ID = emp.ID
WHEN MATCHED THEN DELETE; 
PRINT 'We Successfully Fired Our First INSTEAD OF DELETE Triggers in SQL Server.'
GO

OUTPUT

Let me show you the newly created trigger by opening the Object explorer -> Go to the SQL Tutorial Database -> Go and expand the Employee Table -> and then expand the Triggers Folder

For the demonstration purpose, we are deleting all the records in the Employee table whose Occupation = Professional, or Education = Bachelors.

-- SQL Instead Of DELETE Triggers Example
USE [SQL Tutorial]
GO
DELETE FROM [EmployeeTable]
	WHERE [Occupation] = N'Professional' OR
		[Education] = N'Bachelors'

OUTPUT

From the above screenshot, you can see that, our trigger is triggered. And, instead of deleting 4 records from the Employee table, it is deleting from the Employee Audit table. Please use the following SQL Query to check the inserted records in the Employee table

-- SQL Server Instead Of DELETE Triggers Example
USE [SQL Tutorial]
GO
SELECT [ID]
      ,[Name]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [EmployeeTable]

OUTPUT

Though we performed delete operation on the Employee table. As you can see from the above screenshot, our Employee table is unchanged. Next, check the records in the Employee table Audit using the following query.

-- SQL Instead Of DELETE Triggers Example
USE [SQL Tutorial]
GO

SELECT [Name]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[ServerName]
      ,[ServerInstanceName]
      ,[Insert Time]
  FROM [EmployeeTableAudit]

OUTPUT

Here, you can see that the trigger has deleted the matched records from the Employee audit table