Instead of insert trigger - Part 45

Suggested SQL Server Videos before watching this Video
Part 39 - Views
Part 40 - Updateable Views
Part 43 - DML triggers
Part 44 - DML After Update Trigger







In this video we will learn about, INSTEAD OF triggers, specifically INSTEAD OF INSERT trigger. We know that, AFTER triggers are fired after the triggering event(INSERT, UPDATE or DELETE events), where as, INSTEAD OF triggers are fired instead of the triggering event(INSERT, UPDATE or DELETE events). In general, INSTEAD OF triggers are usually used to correctly update views that are based on multiple tables. 

We will base our demos on Employee and Department tables. So, first, let's create these 2 tables.

SQL Script to create tblEmployee table:
CREATE TABLE tblEmployee
(
Id int Primary Key,
Name nvarchar(30),
Gender nvarchar(10),
DepartmentId int
)

SQL Script to create tblDepartment table 
CREATE TABLE tblDepartment
(
DeptId int Primary Key,
DeptName nvarchar(20)
)

Insert data into tblDepartment table
Insert into tblDepartment values (1,'IT')
Insert into tblDepartment values (2,'Payroll')
Insert into tblDepartment values (3,'HR')
Insert into tblDepartment values (4,'Admin')

Insert data into tblEmployee table
Insert into tblEmployee values (1,'John', 'Male', 3)
Insert into tblEmployee values (2,'Mike', 'Male', 2)
Insert into tblEmployee values (3,'Pam', 'Female', 1)
Insert into tblEmployee values (4,'Todd', 'Male', 4)
Insert into tblEmployee values (5,'Sara', 'Female', 1)
Insert into tblEmployee values (6,'Ben', 'Male', 3)

Since, we now have the required tables, let's create a view based on these tables. The view should return Employee Id, Name, Gender and DepartmentName columns. So, the view is obviously based on multiple tables.

Script to create the view:

Create view vWEmployeeDetails
as
Select Id, Name, Gender, DeptName
from tblEmployee 
join tblDepartment
on tblEmployee.DepartmentId = tblDepartment.DeptId

When you execute, Select * from vWEmployeeDetails, the data from the view, should be as shown below


Now, let's try to insert a row into the view, vWEmployeeDetails, by executing the following query. At this point, an error will be raised stating 'View or function vWEmployeeDetails is not updatable because the modification affects multiple base tables.'
Insert into vWEmployeeDetails values(7, 'Valarie', 'Female', 'IT')

So, inserting a row into a view that is based on multipe tables, raises an error by default. Now, let's understand, how INSTEAD OF TRIGGERS can help us in this situation. Since, we are getting an error, when we are trying to insert a row into the view, let's create an INSTEAD OF INSERT trigger on the view vWEmployeeDetails.

Script to create INSTEAD OF INSERT trigger:
Create trigger tr_vWEmployeeDetails_InsteadOfInsert
on vWEmployeeDetails
Instead Of Insert
as
Begin
Declare @DeptId int

--Check if there is a valid DepartmentId
--for the given DepartmentName
Select @DeptId = DeptId 
from tblDepartment 
join inserted
on inserted.DeptName = tblDepartment.DeptName

--If DepartmentId is null throw an error
--and stop processing
if(@DeptId is null)
Begin
Raiserror('Invalid Department Name. Statement terminated', 16, 1)
return
End

--Finally insert into tblEmployee table
Insert into tblEmployee(Id, Name, Gender, DepartmentId)
Select Id, Name, Gender, @DeptId
from inserted
End

Now, let's execute the insert query:
Insert into vWEmployeeDetails values(7, 'Valarie', 'Female', 'IT')

The instead of trigger correctly inserts, the record into tblEmployee table. Since, we are inserting a row, the inserted table, contains the newly added row, where as the deleted table will be empty.

In the trigger, we used Raiserror() function, to raise a custom error, when the DepartmentName 
provided in the insert query, doesnot exist. 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. We will talk about Raiserror() and exception handling in sql server, in a later video session.

10 comments:

  1. Thanks Venkat...

    I enjoy with your videos...


    Your video's are awesome

    ReplyDelete
  2. So, how does this trigger work for a thousand rows at a time, i.e.

    INSERT INTO tblEmployee(Id, Name, Gender, DepartmentId)
    SELECT Id, Name, Gender, DepartmentID
    FROM tblBulkLoadFromMerger

    when some departmentid's are new and some are not (arbitrary order)?

    ReplyDelete
  3. Sir, Can we Perform Instead of Insert Trigger on Table Having Identity property???
    Bcoz When I performed this operation on a table with IDENTITY, I've got Error regarding Identity property...
    Please Help Me out......

    ReplyDelete
  4. @Abhishek Yadav

    I Think You Should Use This Query

    Create Table TBLEmployee
    (
    Id Int Primary Key Identity Not Null,
    Name Varchar(30) Not Null,
    Gender varchar(20) Not Null,
    DepartMentId int Foreign Key References TBLDepartment(DeptId) Not Null
    )



    Create Table TBLDepartment
    (
    DeptId Int Primary Key Identity Not Null,
    DeptName Varchar(30)
    )



    Insert Into TBLDepartment Values('IT');
    Insert Into TBLDepartment Values('Payroll');
    Insert Into TBLDepartment Values('HR');
    Insert Into TBLDepartment Values('Admin');

    Insert Into TBLEmployee Values('John','Male',3);
    Insert Into TBLEmployee Values('Mike','Male',2);
    Insert Into TBLEmployee Values('Pam','FeMale',1);
    Insert Into TBLEmployee Values('Todd','Male',4);
    Insert Into TBLEmployee Values('Sara','FeMale',1);
    Insert Into TBLEmployee Values('Ben','Male',3);




    Create View VW_Employee_Department
    As
    Select Id,Name,Gender,DeptName From TBLEmployee
    Join
    TBLDepartment
    On
    TBLDepartment.DeptId=TBLEmployee.DepartmentId





    Create Trigger Tr_VW_Employee_Department_Insert
    On VW_Employee_Department
    Instead Of Insert
    As
    Begin
    Declare @DeptId Int
    Select @DeptId=DeptId From TBLDepartment
    Join Inserted On Inserted.DeptName=TBLDepartment.DeptName
    If(@DeptId Is Null)
    Begin
    RaisError('Invalid Department, Statement Terminated',16,1);
    End

    Insert Into TBLEmployee(Name,Gender,DepartmentId) Select Name,Gender,@DeptId From Inserted
    End


    Insert Into VW_Employee_Department Values(8,'Sunil','Male','HR');

    Select* From tblEmployee

    Select* From tblDepartment
    Select* From VW_Employee_Department

    --Drop Table tblEmployee
    --Drop Table tblDepartment;
    --Drop View VW_Employee_Department
    --Drop Trigger Tr_VW_Employee_Department_Insert

    ReplyDelete
  5. Thanks Venkat this is the best example I could come across with instead of trigger, God bless you.

    ReplyDelete
  6. thank you so much. that helps a lot.
    for the guy who provides us with the query in the comment above, I see it's the same thing.

    thank you all and god bless you

    ReplyDelete
  7. Thanks for posting video and written text. I prefer reading text. However, videos are equally helpful.

    ReplyDelete
  8. Video and text very helpful. Easy to follow

    ReplyDelete
  9. I am getting a error when i am using the inserted table. What is the solution

    ReplyDelete
  10. why am i getting an error called invalid column when doing the join with inserted table

    ReplyDelete

It would be great if you can help share these free resources