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.