Instead Of Delete Trigger in SQL Server

Instead Of Delete Trigger in SQL Server

In this article, I will discuss the Instead Of Delete Trigger in SQL Server with some examples. I strongly recommended you to read below articles before proceeding to this article.

DML Triggers in SQL Server

Magic Tables in SQL Server

DML Trigger Real-Time Examples in SQL Server

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

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

Instead Of Delete 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 Delete 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 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 give us an error.

Now let’s understand how the INSTEAD OF TRIGGERS can help us in the 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

It will give us the below result.

Instead Of Delete Trigger in SQL Server

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

SUMMARY

In this article, I try to explain the Instead Of Delete 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.

Leave a Reply

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