Instead Of Trigger in SQL Server

Back to: SQL Server Tutorial For Beginners and Professionals

Instead Of Trigger in SQL Server with Examples

In this article, I am going to discuss the Instead Of Trigger in SQL Server with examples. Please read our previous article where we discussed the For/After Triggers in SQL Server.  At the end of this article, you will understand the following pointers in detail.

1. What is Instead Of Triggers in SQL Server?
2. Example to understand Instead of Triggers in SQL Server
3. INSTEAD OF INSERT trigger in SQL Server
4. Instead Of Update Trigger in SQL Server
5. Instead Of Delete Trigger in SQL Server

What is Instead Of Triggers in SQL Server?

The INSTEAD OF triggers are the DML triggers that are fired instead of the triggering event such as the INSERT, UPDATE or DELETE events. So, when you fire any DML statements such as Insert, Update, and Delete, then on behalf of the DML statement, the instead of trigger is going to execute. In real-time applications, Instead Of Triggers are used to correctly update a complex view.

Example to understand Instead of Triggers in SQL Server:

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 sample 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 insert a record into the view vwEmployeeDetails by executing the following query.
INSERT INTO vwEmployeeDetails VALUES(7, ‘Saroj’, ‘Male’, 50000, ‘IT’)
When we execute the above query it gives us the error as ‘View or function vwEmployeeDetails is not updatable because the modification affects multiple base tables.‘

How to overcome the above problem?

By using Instead of Insert Trigger.

INSTEAD OF INSERT Trigger in SQL Server:

Here you can see that inserting a record into a view that is based on multiple tables gives us an error. Now let’s understand how the INSTEAD OF TRIGGERS can help us in situations like this. As we are getting an error when we are trying to insert a record into the view, let’s create an INSTEAD OF INSERT trigger on the view vwEmployeeDetails to correctly insert the records into the appropriate table.

CREATE TRIGGER tr_vwEmployeeDetails_InsteadOfInsert
ON vwEmployeeDetails
INSTEAD OF INSERT
AS
BEGIN
  DECLARE @DepartmenttId int
 
  -- First Check if there is a valid DepartmentId in the Department Table
  -- for the given Department Name
  SELECT @DepartmenttId = dept.ID 
  FROM Department dept
  INNER JOIN INSERTED inst
  on inst.Department = dept.Name
 
  --If the DepartmentId is null then throw an error
  IF(@DepartmenttId is null)
  BEGIN
    RAISERROR('Invalid Department Name. Statement terminated', 16, 1)
    RETURN
  END
 
  -- Finally insert the data into the Employee table
  INSERT INTO Employee(ID, Name, Gender, Salary, DeptID)
  SELECT ID, Name, Gender, Salary, @DepartmenttId
  FROM INSERTED
End

Now, let’s execute the below Insert statement.

INSERT INTO vwEmployeeDetails VALUES(7, ‘Saroj’, ‘Male’, 50000, ‘IT’)

Instead Of Trigger inserts the row correctly into the Employee table as expected. Since we are inserting a row, the inserted magic table will contain the newly added row whereas the deleted table will be empty. Now check the data by issuing a select query against the Employee Table or the vwEmployeeDetails view.

SELECT * FROM vwEmployeeDetails will give us the below result.

As you can see from the above image, the record is inserted as expected into the Employee table.

Instead Of Update Trigger in SQL Server:

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.

Please update the Department and Employee table as shown below to understand this concept.

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 the Name and Salary column from the Employee table and the 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 comparing values between inserted and deleted tables, rather than relying on the 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

Instead Of Delete Trigger in SQL Server:

The INSTEAD OF DELETE Trigger in SQL server gets fired instead of the DELETE event on a table or a view. For example, let’s say we have an INSTEAD OF DELETE trigger on a view or on a table, and when we try to delete a row from that view or table, instead of the actual DELETE event, the trigger gets fired automatically. INSTEAD OF DELETE TRIGGERS are usually used to delete the records from a view that is based on multiple tables.

Please update the Department and Employee table as shown below to understand this concept.

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 delete a record from the view vwEmployeeDetails by executing the following query.

DELETE FROM vwEmployeeDetails WHERE ID = 1

When we execute the above query it gives us the error as ‘View or function vwEmployeeDetails is not updatable because the modification affects multiple base tables.‘

Here we can see that deleting a record from a view that is based on multiple tables gives us an error. Now let’s understand how the INSTEAD OF TRIGGERS can help us in a situation like this. As we are getting an error when we are trying to Delete a record from the view, let’s create an INSTEAD OF DELETE trigger on the view vwEmployeeDetails to correctly delete the data.

CREATE TRIGGER tr_vwEmployeeDetails_InsteadOfDelete
ON vwEmployeeDetails
INSTEAD OF DELETE
AS
BEGIN
  -- Using Inner Join
  DELETE FROM Employee 
  FROM Employee emp
  INNER JOIN DELETED del
  ON emp.ID = del.ID

  -- Using the Subquery
  -- DELETE FROM Employee 
  -- WHERE ID IN (SELECT ID FROM DELETED)
END

Now, let’s execute the below delete statement.

DELETE FROM vwEmployeeDetails WHERE ID = 1

The Instead Of Trigger deletes the row correctly from the Employee table as expected. Since we are deleting a row, the deleted magic table will contain all the rows that we want to delete whereas the inserted table will be empty. Now check the data by issuing a select query against the Employee Table or the vwEmployeeDetails view.

SELECT * FROM vwEmployeeDetails will give us the below result.

As you can see from the above image, the record is deleted as expected from the Employee table.

In the next article, I am going to discuss DDL Triggers in SQL Server with Examples. In this article, I try to explain the Instead Of Trigger in SQL Server step by step with a real-time example. I hope you enjoy this Instead Of Insert Trigger in SQL Server article. I would like to have your feedback. Please post your feedback, question, or comments about this article.