InsteadOf Trigger in SQL Server

Instead Of Trigger in SQL Server

In this article, I am going to discuss the Instead Of Trigger in SQL Server with examples. I strongly recommended you to read our previous article before proceeding to this article where we discussed the For/After Triggers in SQL Server. 

What are InsteadOf Triggers in SQL Server?

The INSTEAD OF triggers are the 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.

Note: In real-time applications, Instead of Triggers are used to correctly update the view.

We are going to use the following Department and Employee table to understand the complex views in SQL Server.

Instead Of Insert Trigger 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.

Instead Of Insert Trigger in SQL Server

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.

Here we 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

It will give us the below result.

Instead Of Insert Trigger in SQL Server

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

In this article, I try to explain the Instead Of Insert Trigger in SQL Server step by step with a real-time example. I hope this article will help you with your needs. I would like to have your feedback. Please post your feedback, question, or comments about this article.

1 thought on “InsteadOf Trigger in SQL Server”

Leave a Reply

Your email address will not be published. Required fields are marked *