Post
Ask Question

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
  1. CREATE TABLE Student  
  2. (  
  3. Id int Primary Key,  
  4. Name nvarchar(30),  
  5. Gender nvarchar(10),  
  6. SectionId int  
  7. ) 
Table Section SQL script
  1. CREATE TABLE Section  
  2. (  
  3. SecId int Primary Key,  
  4. SecName nvarchar(20)  
  5. )    
Then, insert some dummy records in both the tables.
  1. Insert into Section values (1,'Sec1')  
  2. Insert into Section values (2,'Sec2')  
  3. Insert into Section values (3,'Sec3')  
  4. Insert into Section values (4,'Sec4') 
  1. Insert into Student values (1,'Satya1', 'Male', 3)  
  2. Insert into Student values (2,'Satya2', 'Male', 2)  
  3. Insert into Student values (3,'Satya3', 'Female', 1)  
  4. Insert into Student values (4,'Satya4', 'Male', 4)  
  5. Insert into Student values (5,'Satya5', 'Female', 1)  
  6. 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
  1. Create view ViewStudentDetails  
  2. as  
  3. Select Id, Name, Gender, SecName  
  4. from Student  
  5. join Section  
  6. on Student.SectionId = Section.SecId    
Then, execute the below SQL query. The output will be shown like this. 
  1. 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".
  1. 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
  1. Create trigger TR_ViewStudentDetails_InsteadOfInsert  
  2. on ViewStudentDetails  
  3. Instead Of Insert  
  4. as  
  5. Begin  
  6. Declare @SecId int  
  7.   
  8. Select @SecId = SecId  
  9. from Section  
  10. join inserted  
  11. on inserted.SecName = Section.SecName  
  12.   
  13. if(@SecId is null)  
  14. Begin  
  15. Raiserror('Invalid Section Name. Statement Terminated OK', 16, 1)  
  16. return  
  17. End  
  18.   
  19. Insert into Student(Id, Name, Gender, SectionId)  
  20. Select Id, Name, Gender, @SecId  
  21. from inserted  
  22. End
Now, let's execute the INSERT query using existing section name. The data can be successfully inserted.
  1. Insert into ViewStudentDetails values(7, 'Satya7', 'Female', 'sec4') 
To get the new data, execute the following query.
  1. 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.
  1. 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.