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.

tblEmployee1
tblDepartment1

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

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

Then, insert 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)

Now, execute both the tables to show the results.

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

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 the image below.

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.

Update ViewEmployeeDetails1
set Name = 'Satya1', DeptName = 'Blog'
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.

Update ViewEmployeeDetails1
set DeptName = 'Blog'
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.

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.

Update tblDepartment1 set DeptName = 'Resource' where DeptId = 3

select * from tblDepartment1

Script to create INSTEAD OF UPDATE trigger.

Create Trigger tr_ViewEmployeeDetails1_InsteadOfUpdate
on ViewEmployeeDetails1
instead of update
as
Begin
if(Update(Id))
Begin
Raiserror('Id cannot be changed', 16, 1)
Return
End
if(Update(DeptName))
Begin
Declare @DeptId int
Select @DeptId = DeptId
from tblDepartment1
join inserted
on inserted.DeptName = tblDepartment1.DeptName
if(@DeptId is NULL )
Begin
Raiserror('Invalid Department Name', 16, 1)
Return
End
Update tblEmployee1 set DepartmentId = @DeptId
from inserted
join tblEmployee1
on tblEmployee1.Id = inserted.id
End
if(Update(Gender))
Begin
Update tblEmployee1 set Gender = inserted.Gender
from inserted
join tblEmployee1
on tblEmployee1.Id = inserted.id
End
if(Update(Name))
Begin
Update tblEmployee1 set Name = inserted.Name
from inserted
join tblEmployee1
on tblEmployee1.Id = inserted.id
End
End

Description

Here, trigger name is "tr_ViewEmployeeDetails1_InsteadOfUpdate". It will take action on View ViewEmployeeDetails1". Type of action is "instead of update".

Create Trigger tr_ViewEmployeeDetails1_InsteadOfUpdate
on ViewEmployeeDetails1
instead of update

If EmployeeId is updated, then the following query will be executed.

if(Update(Id))
Begin
Raiserror('Id cannot be changed', 16, 1)
Return
End

If DeptName is updated, then the following query will be executed.

if(Update(DeptName))
Begin
Declare @DeptId int
Select @DeptId = DeptId
from tblDepartment1
join inserted
on inserted.DeptName = tblDepartment1.DeptName
if(@DeptId is NULL )
Begin
Raiserror('Invalid Department Name', 16, 1)
Return
End
Update tblEmployee1 set DepartmentId = @DeptId
from inserted
join tblEmployee1
on tblEmployee1.Id = inserted.id
End

If gender is updated, then this query will be executed.

if(Update(Gender))
Begin
Update tblEmployee1 set Gender = inserted.Gender
from inserted
join tblEmployee1
on tblEmployee1.Id = inserted.id
End

If Name is updated, the following query will be executed.

if(Update(Name))
Begin
Update tblEmployee1 set Name = inserted.Name
from inserted
join tblEmployee1
on tblEmployee1.Id = inserted.id
End

Now, update Satya1's Department to Blog.

Update ViewEmployeeDetails1
set DeptName = 'Blog'
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.'

Update ViewEmployeeDetails1
set Name = 'Satya11', Gender = 'Female', DeptName = 'Blog'
where Id = 1

If we put any invalid department name, the Raiserror() function will show corresponding warning message.

Update ViewEmployeeDetails1
set Name = 'Satya11', Gender = 'Female', DeptName = 'BlogN'
where Id = 1

If we update the id, then the Raiserror() will show corresponding warning message.

Update ViewEmployeeDetails1
set Name = 'Satya11', Gender = 'Female', DeptName = 'BlogN' , id=11
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.