Instead Of Update Trigger in SQL Server
In this article, I will discuss the Instead Of Update Trigger in SQL Server with some examples. I strongly recommended you to read below articles before proceeding to this article.
The INSTEAD OF UPDATE Trigger in SQL server gets fired instead of the UPDATE event on a table or a view. For example, let’s say we have an INSTEAD OF UPDATE trigger on a view or on a table, and when we try to update a record (or records) from that view or table, instead of the actual UPDATE event, the trigger gets fired automatically. The Instead Of Update Trigger in SQL Server is usually used to correctly update the records from a view that is based on multiple tables.
We are going to use the following Department and Employee table to understand the complex views in SQL Server.
Please use the below SQL Script to create and populate the Department and Employee table with some test data.
-- Create Department Table CREATE TABLE Department ( ID INT PRIMARY KEY, Name VARCHAR(50) ) GO -- Populate the Department Table with test data INSERT INTO Department VALUES(1, 'IT') INSERT INTO Department VALUES(2, 'HR') INSERT INTO Department VALUES(3, 'Sales') -- Create Employee Table CREATE TABLE Employee ( ID INT PRIMARY KEY, Name VARCHAR(50), Gender VARCHAR(50), DOB DATETIME, Salary DECIMAL(18,2), DeptID INT ) GO -- Populate the Employee Table with test data INSERT INTO Employee VALUES(1, 'Pranaya', 'Male','1996-02-29 10:53:27.060', 25000, 1) INSERT INTO Employee VALUES(2, 'Priyanka', 'Female','1995-05-25 10:53:27.060', 30000, 2) INSERT INTO Employee VALUES(3, 'Anurag', 'Male','1995-04-19 10:53:27.060',40000, 2) INSERT INTO Employee VALUES(4, 'Preety', 'Female','1996-03-17 10:53:27.060', 35000, 3) INSERT INTO Employee VALUES(5, 'Sambit', 'Male','1997-01-15 10:53:27.060', 27000, 1) INSERT INTO Employee VALUES(6, 'Hina', 'Female','1995-07-12 10:53:27.060', 33000, 2) GO
We want to retrieve the following data from the Employee and Department table.
So, let’s create a view that will return the above results.
CREATE VIEW vwEmployeeDetails AS SELECT emp.ID, emp.Name, Gender, Salary, dept.Name AS Department FROM Employee emp INNER JOIN Department dept ON emp.DeptID = dept.ID
Now let’s try to update the view vwEmployeeDetails in such a way that it affects both the underlying tables such as Employee and Department table and see if we get any error.
The following UPDATE statement changes Name and Salary column from the Employee table and Department Name column from the Department table.
UPDATE vwEmployeeDetails SET Name = 'Kumar', Salary = 45000, Department = 'HR' WHERE Id = 1
When we execute the above update query, we get the error as “View or function ‘vwEmployeeDetails’ is not updatable because the modification affects multiple base tables.”
Note: When the view is based on multiple tables and if the update statement affects more than one table then the update failed.
Now let’s try to change only the department of Pranaya from IT to HR. The following UPDATE query affects only one base table that is the Department table. So, the update query should succeed. But before executing the query please note that employee Sambit is also in the IT department.
UPDATE vwEmployeeDetails SET Department = 'HR' WHERE Id = 1
Once we execute the above query, then select the data from the view and notice that Sambit’s Department is also changed to HR.
SELECT * FROM vwEmployeeDetails
We intended to just change Pranaya’s Department Name but it also changes the Department Name of Sambit. So the UPDATE didn’t work as expected. This is because the UPDATE query updated the Department Name from IT to HR in the Department table.
SELECT * FROM Department
As you can see the Record with Id = 1, has the Department Name changed from IT to HR
So, the conclusion is that, if a view is based on multiple tables, and if we want to update the view, the UPDATE may not always work as expected. To correctly update the underlying base tables, through a view, the Instead Of Update Trigger in SQL Server can be used.
Before we create the trigger, let’s update the Department Name to IT for record with Id = 1.
UPDATE Department SET Name = ‘IT’ WHERE ID = 1
Please use the below SQL Script to create the Instead Of Update Trigger in SQL Server
CREATE TRIGGER tr_vwEmployeeDetails_InsteadOfUpdate ON vwEmployeeDetails INSTEAD OF UPDATE AS BEGIN -- if EmployeeId is updated IF(UPDATE(ID)) BEGIN RAISERROR('Id cannot be changed', 16, 1) RETURN END -- If Department Name is updated IF(UPDATE(Department)) BEGIN DECLARE @DepartmentID INT SELECT @DepartmentID = dept.ID FROM Department dept INNER JOIN INSERTED inst ON dept.Name = inst.Department IF(@DepartmentID is NULL ) BEGIN RAISERROR('Invalid Department Name', 16, 1) RETURN END UPDATE Employee set DeptID = @DepartmentID FROM INSERTED INNER JOIN Employee on Employee.ID = inserted.ID End -- If gender is updated IF(UPDATE(Gender)) BEGIN UPDATE Employee SET Gender = inserted.Gender FROM INSERTED INNER JOIN Employee ON Employee.ID = INSERTED.ID END -- If Salary is updated IF(UPDATE(Salary)) BEGIN UPDATE Employee SET Salary = inserted.Salary FROM INSERTED INNER JOIN Employee ON Employee.ID = INSERTED.ID END -- If Name is updated IF(UPDATE(Name)) BEGIN UPDATE Employee SET Name = inserted.Name FROM INSERTED INNER JOIN Employee ON Employee.ID = INSERTED.ID END END
Note: The Update() function used in the trigger returns true, even if we update with the same value. For this reason, I recommended to compare values between inserted and deleted tables, rather than relying on Update() function. The Update() function does not operate on a per row basis but across all rows.
Now, let’s try to update Pranaya’s Department to HR.
UPDATE vwEmployeeDetails SET Department = ‘HR’ WHERE Id = 1
The UPDATE query works as expected. The INSTEAD OF UPDATE trigger, correctly updates, Pranaya’s DeptId to 2 in Employee table.
Now, let’s try to update Name, Gender, Salary and Department Name. The UPDATE query, works as expected, without raising the error – ‘View or function vwEmployeeDetails is not updatable because the modification affects multiple base tables.‘
UPDATE vwEmployeeDetails SET Name = 'Preety', Gender = 'Female', Salary = 44000, Department = 'IT' WHERE Id = 1
In the next article, I will discuss DDL Triggers in SQL Server with some real-time examples.
In this article, I try to explain the Instead Of Update Trigger in SQL Server step by step with a real-time example. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.