Post
Ask Question

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
  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')   
  5.   
  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  
  2.   
  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.
Next Recommended Reading INSTEAD OF UPDATE Triggers And View