Introduction

INSTEAD OF DELETE triggers are used to delete records from a View that is based on multiple tables.

Description

An INSTEAD OF DELETE trigger gets executed in place of the DELETE event on a table or a View. We keep an INSTEAD OF DELETE trigger on a View or a table, and when we try to update a row from that View or table, instead of the actual DELETE event, the trigger gets fired automatically.

Note about Instead of Insert,Instead of Delete,Instead of Update

Instead of Insert -->> DELETED table is always empty and the INSERTED table contains the newly inserted data.
Instead of Delete -->> INSERTED table is always empty and the DELETED table contains the rows deleted.
Instead of Update -->> DELETED table contains OLD data before modify,and inserted table contains NEW data and Updated data.
Steps to follow

First, create two tables.

SQL Script to create tblEmployee1 table
  1. CREATE TABLE tblEmployee1
  2. (
  3. Id int Primary Key,
  4. Name nvarchar(30),
  5. Gender nvarchar(10),
  6. DepartmentId int
  7. )
SQL Script to create tblDepartment1 table.
  1. CREATE TABLE tblDepartment1
  2. (
  3. DeptId int Primary Key,
  4. DeptName nvarchar(20)
  5. )
Then, enter some dummy records in both the tables.
  1. Insert into tblDepartment1 values (1,'Blog')
  2. Insert into tblDepartment1 values (2,'Article')
  3. Insert into tblDepartment1 values (3,'Resource')
  4. Insert into tblDepartment1 values (4,'Book')
  6. Insert into tblEmployee1 values (1,'Satya1', 'Male', 3)
  7. Insert into tblEmployee1 values (2,'Satya2', 'Male', 2)
  8. Insert into tblEmployee1 values (3,'Satya3', 'Female', 1)
  9. Insert into tblEmployee1 values (4,'Satya4', 'Male', 4)
  10. Insert into tblEmployee1 values (5,'Satya5', 'Female', 1)
  11. Insert into tblEmployee1 values (6,'Satya6', 'Male', 3)
Execute the below query to get details of data.
  1. select * from tblEmployee1
  2. select * from tblDepartment1
Create a View based on these tables. The View should return Employee Id,Employee Name,Employee Gender and Employee DepartmentName columns so that it can be based on multiple tables.

Script to create the view
  1. Create view ViewEmployeeDetails1
  2. as
  3. Select Id, Name, Gender, DeptName
  4. from tblEmployee1
  5. join tblDepartment1
  6. on tblEmployee1.DepartmentId = tblDepartment1.DeptId
When you execute the data from the View, it should look like below.
  1. Select * from ViewEmployeeDetails1
Now, delete a row from the View, and we get the same error.
  1. Delete from ViewEmployeeDetails1 where Id = 1
Script to create INSTEAD OF DELETE trigger using Join
  1. Create Trigger tr_ViewEmployeeDetails1_InsteadOfDelete
  2. on ViewEmployeeDetails1
  3. instead of delete
  4. as
  5. Begin
  6. Declare @Id int
  7. Select @Id = tblEmployee1.Id
  8. from tblEmployee1
  9. join deleted
  10. on deleted.Id = tblEmployee1.Id
  11. if(@Id is NULL )
  12. Begin
  13. Raiserror('Invalid Employee ID or Employee ID not Exists', 16, 1)
  14. Return
  15. End
  16. else
  17. Delete tblEmployee1
  18. from tblEmployee1
  19. join deleted
  20. on tblEmployee1.Id = deleted.Id
  21. End
The trigger tr_ViewEmployeeDetails1_InsteadOfDelete makes use of DELETED table. DELETED table contains all the rows that we tried to delete from the View. So, we are joining the DELETED table with tblEmployee1, to delete the rows.

You can also use sub-queries to do the same. In most cases, JOINs are faster than sub-queries.
Script to create INSTEAD OF DELETE trigger using Subquery
  1. Create Trigger tr_ViewEmployeeDetails1_InsteadOfDelete
  2. on ViewEmployeeDetails1
  3. instead of delete
  4. as
  5. Begin
  6. Declare @Id int
  7. Select @Id = tblEmployee1.Id
  8. from tblEmployee1
  9. join deleted
  10. on deleted.Id = tblEmployee1.Id
  11. if(@Id is NULL )
  12. Begin
  13. Raiserror('Invalid Employee ID or Employee ID not Exists', 16, 1)
  14. Return
  15. End
  16. else
  17. --using Subquery
  18. Delete from tblEmployee1
  19. where Id in (Select Id from deleted)
  20. End
In the following DELETE SQL statement, the row gets DELETED as expected from tblEmployee1 table.
  1. Delete from ViewEmployeeDetails1 where Id = 1
Then, execute the below mentioned queries to get status of data after deletion.
  1. Select * from ViewEmployeeDetails1
  3. Select * from tblEmployee1
If we put wrong Employee ID using View, the SQL statement for delete will be

Summary

In this blog, we learned the following.
  1. What is INSTEAD OF DELETE trigger.
  2. How to implement INSTEAD OF DELETE trigger on View and table.
  3. Using subquery and joins in INSTEAD OF DELETE trigger.