Post
Ask Question

INSTEAD OF UPDATE Triggers And View

Introduction

INSTEAD OF UPDATE triggers correctly update a View that is based on multiple tables.
 
Description

This INSTEAD OF UPDATE trigger is executed instead of an update event, on a table or a View. We put an INSTEAD OF UPDATE trigger on a View or a table, and when the table/View is updated, in place of UPDATE trigger, the INSTEAD OF UPDATE trigger gets fired automatically.

Notes

Update() function used in the trigger returns true, even if you update with the same value. The Update() function does not operate on a per row basis, but across all rows. 
 
Steps to follow

First, create two tables. 
  1. tblEmployee1 
  2. tblDepartment1
  1. CREATE TABLE tblEmployee1  
  2. (  
  3.  Id int Primary Key,  
  4.  Name nvarchar(30),  
  5.  Gender nvarchar(10),  
  6.  DepartmentId int  
  7. ) 
  1. CREATE TABLE tblDepartment1  
  2. (  
  3.  DeptId int Primary Key,  
  4.  DeptName nvarchar(20)  
  5. ) 
Then, insert 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') 
  1. Insert into tblEmployee1 values (1,'Satya1', 'Male', 3)  
  2. Insert into tblEmployee1 values (2,'Satya2', 'Male', 2)  
  3. Insert into tblEmployee1 values (3,'Satya3', 'Female', 1)  
  4. Insert into tblEmployee1 values (4,'Satya4', 'Male', 4)  
  5. Insert into tblEmployee1 values (5,'Satya5', 'Female', 1)  
  6. Insert into tblEmployee1 values (6,'Satya6', 'Male', 3) 
Now, execute both the tables to show the results.
  1. select * from tblDepartment1  
  2.   
  3. select * from tblEmployee1 
 
 
Then, create a View based on these two tables. The View should return Employee Id, Employee Name, Employee Gender, and Employee DepartmentName columns. So, the View is obviously 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 the image below.
  1. Select * from ViewEmployeeDetails1 
 
 
When we update the View, it affects both these tables and we get an error. The View's UPDATE statement changes Name column from tblEmployee1 and DeptName column from tblDepartment1. So, when we execute this query, we get the same error. 
  1. Update ViewEmployeeDetails1  
  2. set Name = 'Satya1', DeptName = 'Blog'  
  3. where Id = 1 
 
 
 
Now, let's try to modify just the department of satya1 from "Resource" to "Blog". The following UPDATE query affects only one table, tblDepartment1. So, the query should succeed. But, before executing the query, please note that employees satya1 and satya6 are in Resource department.
  1. Update ViewEmployeeDetails1   
  2. set DeptName = 'Blog'  
  3. where Id = 1  
After executing the query, select the data from the View and notice that satya6's DeptName is also changed to Blog. Our aim here is to just change satya1's DeptName. So, UPDATE didn't work as expected.

Because the UPDATE query updated the DeptName from Resource to Blog, in tblDepartment1 table, for the UPDATE to work correctly, we should change the DeptId of satya1 from 3 to 1.
  1. Select * from ViewEmployeeDetails1 
 
 
Incorrectly updated View.
 
 
 
Record with Id = 3 in tblDepartment1 has the DeptName changed from 'Resource' to 'Blog'.
 
 
 
We should have actually updated Satya1's DepartmentId from 3 to 1 in tblEmployee1.
 
 
 
A vView is based on multiple tables, and if you update the View, then UPDATE may not always work as expected.
To correctly update the underlying base tables through a View, INSTEAD OF UPDATE Trigger can be used.

Before we create this INSTEAD OF UPDATE Trigger, let's update the DeptName to Resource for record with Id = 3.  
  1. Update tblDepartment1 set DeptName = 'Resource' where DeptId = 3 
  1. select * from tblDepartment1 
 
 
Script to create INSTEAD OF UPDATE trigger.
  1. Create Trigger tr_ViewEmployeeDetails1_InsteadOfUpdate  
  2. on ViewEmployeeDetails1  
  3. instead of update  
  4. as  
  5. Begin  
  6.  if(Update(Id))  
  7.  Begin  
  8.   Raiserror('Id cannot be changed', 16, 1)  
  9.   Return  
  10.  End  
  11.    
  12.  if(Update(DeptName))   
  13.  Begin  
  14.   Declare @DeptId int  
  15.   
  16.   Select @DeptId = DeptId  
  17.   from tblDepartment1  
  18.   join inserted  
  19.   on inserted.DeptName = tblDepartment1.DeptName  
  20.     
  21.   if(@DeptId is NULL )  
  22.   Begin  
  23.    Raiserror('Invalid Department Name', 16, 1)  
  24.    Return  
  25.   End  
  26.     
  27.   Update tblEmployee1 set DepartmentId = @DeptId  
  28.   from inserted  
  29.   join tblEmployee1  
  30.   on tblEmployee1.Id = inserted.id  
  31.  End  
  32.    
  33.  if(Update(Gender))  
  34.  Begin  
  35.   Update tblEmployee1 set Gender = inserted.Gender  
  36.   from inserted  
  37.   join tblEmployee1  
  38.   on tblEmployee1.Id = inserted.id  
  39.  End  
  40.    
  41.  if(Update(Name))  
  42.  Begin  
  43.   Update tblEmployee1 set Name = inserted.Name  
  44.   from inserted  
  45.   join tblEmployee1  
  46.   on tblEmployee1.Id = inserted.id  
  47.  End  
  48. End 
Description
 
Here, trigger name is "tr_ViewEmployeeDetails1_InsteadOfUpdate". It will take action on View ViewEmployeeDetails1". Type of action is "instead of update". 
  1. Create Trigger tr_ViewEmployeeDetails1_InsteadOfUpdate  
  2. on ViewEmployeeDetails1  
  3. instead of update 
If EmployeeId is updated, then the following query will be executed.
  1. if(Update(Id))  
  2. Begin  
  3.  Raiserror('Id cannot be changed', 16, 1)  
  4.  Return  
  5. End 
 If DeptName is updated, then the following query will be executed.
  1. if(Update(DeptName))   
  2. Begin  
  3.  Declare @DeptId int  
  4.   
  5.  Select @DeptId = DeptId  
  6.  from tblDepartment1  
  7.  join inserted  
  8.  on inserted.DeptName = tblDepartment1.DeptName  
  9.    
  10.  if(@DeptId is NULL )  
  11.  Begin  
  12.   Raiserror('Invalid Department Name', 16, 1)  
  13.   Return  
  14.  End  
  15.    
  16.  Update tblEmployee1 set DepartmentId = @DeptId  
  17.  from inserted  
  18.  join tblEmployee1  
  19.  on tblEmployee1.Id = inserted.id  
  20. End  
If gender is updated, then this query will be executed.
  1. if(Update(Gender))  
  2. Begin  
  3.  Update tblEmployee1 set Gender = inserted.Gender  
  4.  from inserted  
  5.  join tblEmployee1  
  6.  on tblEmployee1.Id = inserted.id  
  7. End 
 If Name is updated, the following query will be executed.
  1. if(Update(Name))  
  2. Begin  
  3.  Update tblEmployee1 set Name = inserted.Name  
  4.  from inserted  
  5.  join tblEmployee1  
  6.  on tblEmployee1.Id = inserted.id  
  7. End 
Now, update Satya1's Department to Blog.
  1. Update ViewEmployeeDetails1   
  2. set DeptName = 'Blog'  
  3. where Id = 1 
 
 
The UPDATE query works as expected. The INSTEAD OF UPDATE trigger correctly updates Satya1's DepartmentId to 1 of tblEmployee1 table. 
 
 
 
Now, let's try to update Name, Gender, and DeptName. The UPDATE query works as expected, without raising the error. That is 'View or function ViewEmployeeDetails1 is not updatable because the modification affects multiple base tables.' 
  1. Update ViewEmployeeDetails1   
  2. set Name = 'Satya11', Gender = 'Female', DeptName = 'Blog'   
  3. where Id = 1 
 
 
If we put any invalid department name, the Raiserror() function will show corresponding warning message.
  1. Update ViewEmployeeDetails1   
  2. set Name = 'Satya11', Gender = 'Female', DeptName = 'BlogN'   
  3. where Id = 1 
 
 
If we update the id, then the Raiserror() will show corresponding warning message. 
  1. Update ViewEmployeeDetails1   
  2. set Name = 'Satya11', Gender = 'Female', DeptName = 'BlogN' , id=11  
  3. where Id = 1 
 
 
Summary

We learned what INSTEAD OF UPDATE Trigger is and  how to update View using it correctly.

I hope, you liked it. 
Next Recommended Reading INSTEAD OF DELETE Triggers And View