INSTEAD OF INSERT Trigger And View In SQL Server

Introduction

"AFTER" triggers are executed after the triggering events like INSERT, UPDATE or DELETE events, where as the "INSTEAD OF" triggers are fired in place of the triggering events. INSTEAD OF triggers are usually used to correctly update views that are based on multiple tables.

Steps to be followed to use INSTEAD OF INSERT trigger

So, first, let's create two tables named Student & Section.

Table Student SQL script

CREATE TABLE Student
(
Id int Primary Key,
Name nvarchar(30),
Gender nvarchar(10),
SectionId int
)

Table Section SQL script

CREATE TABLE Section
(
SecId int Primary Key,
SecName nvarchar(20)
)

Then, insert some dummy records in both the tables.

Insert into Section values (1,'Sec1')
Insert into Section values (2,'Sec2')
Insert into Section values (3,'Sec3')
Insert into Section values (4,'Sec4')

Insert into Student values (1,'Satya1', 'Male', 3)
Insert into Student values (2,'Satya2', 'Male', 2)
Insert into Student values (3,'Satya3', 'Female', 1)
Insert into Student values (4,'Satya4', 'Male', 4)
Insert into Student values (5,'Satya5', 'Female', 1)
Insert into Student values (6,'Satya6', 'Male', 3)

Let's create a View based on the above mentioned tables.

The View should return Student Id, Name, Gender, SecName columns. So, it is obviously based on multiple tables.

Script to create the View

Create view ViewStudentDetails
as
Select Id, Name, Gender, SecName
from Student
join Section
on Student.SectionId = Section.SecId

Then, execute the below SQL query. The output will be shown like this.

Select * from ViewStudentDetails

Let's try to insert a row into the View, ViewStudentDetails, by executing the following query. An error will be raised showing "View or function ViewStudentDetails is not updatable because the modification affects multiple base tables".

Insert into ViewStudentDetails values(7, 'Valarie', 'Female', 'Sec4')

So, inserting a row into a View that is based on multipe tables, raises an error by default. Let's create an INSTEAD OF INSERT trigger on the ViewStudentDetails view.

Script to create INSTEAD OF INSERT trigger

Create trigger TR_ViewStudentDetails_InsteadOfInsert
on ViewStudentDetails
Instead Of Insert
as
Begin
Declare @SecId int
Select @SecId = SecId
from Section
join inserted
on inserted.SecName = Section.SecName
if(@SecId is null)
Begin
Raiserror('Invalid Section Name. Statement Terminated OK', 16, 1)
return
End
Insert into Student(Id, Name, Gender, SectionId)
Select Id, Name, Gender, @SecId
from inserted
End

Now, let's execute the INSERT query using existing section name. The data can be successfully inserted.

Insert into ViewStudentDetails values(7, 'Satya7', 'Female', 'sec4')

To get the new data, execute the following query.

Select * from ViewStudentDetails

Now, let's execute the insert query using new section name. We can see that the data can't be successfully inserted.

Insert into ViewStudentDetails values(7, 'Satya7', 'Female', 'sec5')

Here, we are getting one error message as we used in Raiserror() function using Instead of insert trigger.

What Is Raiserror() function In Sql server.

The Raiserror() function is used to raise a custom error when the SectionName provided in the insert query does not exist or is not found, like "sec5" in my previous query. We are passing 3 parameters to the Raiserror() method. The first parameter is the error message, the second parameter is the severity level. Severity level 16 indicates general errors that can be corrected by the user. The final parameter is the state.

Summary

We learned what is INSTEAD OF INSERT trigger, how to implement it in real time scenario, and working on it using View.

I hope you liked the blog.

1 Comment

Join the conversation! Your thoughts help the community grow.

Yauheni Leaniuk5y ago
Notice that inside trigger body we are using JOIN which is equal to INNER JOIN but not LEFT JOIN. As a novice with triggers I'm trying to do this with LEFT JOIN instead and I stuck with this for a many hours, so maybe this will be helpful for someone