INSTEAD OF DELETE Triggers And View

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

CREATE TABLE tblEmployee1
(
Id int Primary Key,
Name nvarchar(30),
Gender nvarchar(10),
DepartmentId int
)

SQL Script to create tblDepartment1 table.

CREATE TABLE tblDepartment1
(
DeptId int Primary Key,
DeptName nvarchar(20)
)

Then, enter some dummy records in both the tables.

Insert into tblDepartment1 values (1,'Blog')
Insert into tblDepartment1 values (2,'Article')
Insert into tblDepartment1 values (3,'Resource')
Insert into tblDepartment1 values (4,'Book')
Insert into tblEmployee1 values (1,'Satya1', 'Male', 3)
Insert into tblEmployee1 values (2,'Satya2', 'Male', 2)
Insert into tblEmployee1 values (3,'Satya3', 'Female', 1)
Insert into tblEmployee1 values (4,'Satya4', 'Male', 4)
Insert into tblEmployee1 values (5,'Satya5', 'Female', 1)
Insert into tblEmployee1 values (6,'Satya6', 'Male', 3)

Execute the below query to get details of data.

select * from tblEmployee1
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

Create view ViewEmployeeDetails1
as
Select Id, Name, Gender, DeptName
from tblEmployee1
join tblDepartment1
on tblEmployee1.DepartmentId = tblDepartment1.DeptId

When you execute the data from the View, it should look like below.

Select * from ViewEmployeeDetails1

Now, delete a row from the View, and we get the same error.

Delete from ViewEmployeeDetails1 where Id = 1

Script to create INSTEAD OF DELETE trigger using Join

Create Trigger tr_ViewEmployeeDetails1_InsteadOfDelete
on ViewEmployeeDetails1
instead of delete
as
Begin
Declare @Id int
Select @Id = tblEmployee1.Id
from tblEmployee1
join deleted
on deleted.Id = tblEmployee1.Id
if(@Id is NULL )
Begin
Raiserror('Invalid Employee ID or Employee ID not Exists', 16, 1)
Return
End
else
Delete tblEmployee1
from tblEmployee1
join deleted
on tblEmployee1.Id = deleted.Id
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

Create Trigger tr_ViewEmployeeDetails1_InsteadOfDelete
on ViewEmployeeDetails1
instead of delete
as
Begin
Declare @Id int
Select @Id = tblEmployee1.Id
from tblEmployee1
join deleted
on deleted.Id = tblEmployee1.Id
if(@Id is NULL )
Begin
Raiserror('Invalid Employee ID or Employee ID not Exists', 16, 1)
Return
End
else
--using Subquery
Delete from tblEmployee1
where Id in (Select Id from deleted)
End

In the following DELETE SQL statement, the row gets DELETED as expected from tblEmployee1 table.

Delete from ViewEmployeeDetails1 where Id = 1

Then, execute the below mentioned queries to get status of data after deletion.

Select * from ViewEmployeeDetails1
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.

What is INSTEAD OF DELETE trigger.
How to implement INSTEAD OF DELETE trigger on View and table.
Using subquery and joins in INSTEAD OF DELETE trigger.