How to Create and Use INSTEAD OF INSERT Triggers in SQL Server: A Comprehensive Guide
☰Fullscreen
In this tutorial 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:
<span class="pln"> CREATE TABLE tblEmployee </span><span class="pun">(</span><span class="pln"> </span><span class="typ">Id</span><span class="pln"> </span><span class="kwd">int</span><span class="pln"> </span><span class="typ">Primary</span><span class="pln"> </span><span class="typ">Key</span><span class="pun">,</span><span class="pln"> </span><span class="typ">Name</span><span class="pln"> nvarchar</span><span class="pun">(</span><span class="lit">30</span><span class="pun">),</span><span class="pln"> </span><span class="typ">Gender</span><span class="pln"> nvarchar</span><span class="pun">(</span><span class="lit">10</span><span class="pun">),</span><span class="pln"> </span><span class="typ">DepartmentId</span><span class="pln"> </span><span class="kwd">int</span><span class="pln"> </span><span class="pun">)</span><span class="pln"> </span>
SQL Script to create tblDepartment table
<span class="pln"> CREATE TABLE tblDepartment </span><span class="pun">(</span><span class="pln"> </span><span class="typ">DeptId</span><span class="pln"> </span><span class="kwd">int</span><span class="pln"> </span><span class="typ">Primary</span><span class="pln"> </span><span class="typ">Key</span><span class="pun">,</span><span class="pln"> </span><span class="typ">DeptName</span><span class="pln"> nvarchar</span><span class="pun">(</span><span class="lit">20</span><span class="pun">)</span><span class="pln"> </span><span class="pun">)</span><span class="pln"> </span>
Insert data into tblDepartment table
<span class="pln"> </span><span class="typ">Insert</span><span class="pln"> </span><span class="kwd">into</span><span class="pln"> tblDepartment values </span><span class="pun">(</span><span class="lit">1</span><span class="pun">,</span><span class="str">'IT'</span><span class="pun">)</span><span class="pln"> </span><span class="typ">Insert</span><span class="pln"> </span><span class="kwd">into</span><span class="pln"> tblDepartment values </span><span class="pun">(</span><span class="lit">2</span><span class="pun">,</span><span class="str">'Payroll'</span><span class="pun">)</span><span class="pln"> </span><span class="typ">Insert</span><span class="pln"> </span><span class="kwd">into</span><span class="pln"> tblDepartment values </span><span class="pun">(</span><span class="lit">3</span><span class="pun">,</span><span class="str">'HR'</span><span class="pun">)</span><span class="pln"> </span><span class="typ">Insert</span><span class="pln"> </span><span class="kwd">into</span><span class="pln"> tblDepartment values </span><span class="pun">(</span><span class="lit">4</span><span class="pun">,</span><span class="str">'Admin'</span><span class="pun">)</span><span class="pln"> </span>
Code:
<span class="pln"> </span><span class="typ">Insert</span><span class="pln"> data </span><span class="kwd">into</span><span class="pln"> tblEmployee table </span><span class="typ">Insert</span><span class="pln"> </span><span class="kwd">into</span><span class="pln"> tblEmployee values </span><span class="pun">(</span><span class="lit">1</span><span class="pun">,</span><span class="str">'Rambo'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Male'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">3</span><span class="pun">)</span><span class="pln"> </span><span class="typ">Insert</span><span class="pln"> </span><span class="kwd">into</span><span class="pln"> tblEmployee values </span><span class="pun">(</span><span class="lit">2</span><span class="pun">,</span><span class="str">'Shyam'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Male'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">2</span><span class="pun">)</span><span class="pln"> </span><span class="typ">Insert</span><span class="pln"> </span><span class="kwd">into</span><span class="pln"> tblEmployee values </span><span class="pun">(</span><span class="lit">3</span><span class="pun">,</span><span class="str">'Sama'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Female'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pun">)</span><span class="pln"> </span><span class="typ">Insert</span><span class="pln"> </span><span class="kwd">into</span><span class="pln"> tblEmployee values </span><span class="pun">(</span><span class="lit">4</span><span class="pun">,</span><span class="str">'Jon'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Male'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">4</span><span class="pun">)</span><span class="pln"> </span><span class="typ">Insert</span><span class="pln"> </span><span class="kwd">into</span><span class="pln"> tblEmployee values </span><span class="pun">(</span><span class="lit">5</span><span class="pun">,</span><span class="str">'Sara'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Female'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pun">)</span><span class="pln"> </span><span class="typ">Insert</span><span class="pln"> </span><span class="kwd">into</span><span class="pln"> tblEmployee values </span><span class="pun">(</span><span class="lit">6</span><span class="pun">,</span><span class="str">'Ram'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Male'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">3</span><span class="pun">)</span><span class="pln"> </span>
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:
<span class="pln"> </span><span class="typ">Create</span><span class="pln"> view vWEmployeeDetails </span><span class="kwd">as</span><span class="pln"> </span><span class="typ">Select</span><span class="pln"> </span><span class="typ">Id</span><span class="pun">,</span><span class="pln"> </span><span class="typ">Name</span><span class="pun">,</span><span class="pln"> </span><span class="typ">Gender</span><span class="pun">,</span><span class="pln"> </span><span class="typ">DeptName</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> tblEmployee </span><span class="kwd">join</span><span class="pln"> tblDepartment on tblEmployee</span><span class="pun">.</span><span class="typ">DepartmentId</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> tblDepartment</span><span class="pun">.</span><span class="typ">DeptId</span><span class="pln"> </span>
When you execute, Select * from vWEmployeeDetails, the data from the view, should be as shown below
<span class="pln"> </span><span class="typ">Select</span><span class="pln"> </span><span class="pun">*</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> vWEmployeeDetails </span>
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.'
Code:
<span class="pln"> </span><span class="typ">Insert</span><span class="pln"> </span><span class="kwd">into</span><span class="pln"> vWEmployeeDetails values</span><span class="pun">(</span><span class="lit">7</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Valarie'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Female'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'IT'</span><span class="pun">)</span><span class="pln"> </span>
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:
<span class="pln"> </span><span class="typ">Create</span><span class="pln"> trigger tr_vWEmployeeDetails_InsteadOfInsert on vWEmployeeDetails </span><span class="typ">Instead</span><span class="pln"> </span><span class="typ">Of</span><span class="pln"> </span><span class="typ">Insert</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> </span><span class="typ">Begin</span><span class="pln"> </span><span class="typ">Declare</span><span class="pln"> </span><span class="lit">@DeptId</span><span class="pln"> </span><span class="kwd">int</span><span class="pln"> </span><span class="pun">--</span><span class="typ">Check</span><span class="pln"> </span><span class="kwd">if</span><span class="pln"> there </span><span class="kwd">is</span><span class="pln"> a valid </span><span class="typ">DepartmentId</span><span class="pln"> </span><span class="pun">--</span><span class="kwd">for</span><span class="pln"> the given </span><span class="typ">DepartmentName</span><span class="pln"> </span><span class="typ">Select</span><span class="pln"> </span><span class="lit">@DeptId</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="typ">DeptId</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> tblDepartment </span><span class="kwd">join</span><span class="pln"> inserted on inserted</span><span class="pun">.</span><span class="typ">DeptName</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> tblDepartment</span><span class="pun">.</span><span class="typ">DeptName</span><span class="pln"> </span><span class="pun">--</span><span class="typ">If</span><span class="pln"> </span><span class="typ">DepartmentId</span><span class="pln"> </span><span class="kwd">is</span><span class="pln"> </span><span class="kwd">null</span><span class="pln"> </span><span class="kwd">throw</span><span class="pln"> an error </span><span class="pun">--</span><span class="kwd">and</span><span class="pln"> stop processing </span><span class="kwd">if</span><span class="pun">(</span><span class="lit">@DeptId</span><span class="pln"> </span><span class="kwd">is</span><span class="pln"> </span><span class="kwd">null</span><span class="pun">)</span><span class="pln"> </span><span class="typ">Begin</span><span class="pln"> </span><span class="typ">Raiserror</span><span class="pun">(</span><span class="str">'Invalid Department Name. Statement terminated'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">16</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pun">)</span><span class="pln"> </span><span class="kwd">return</span><span class="pln"> </span><span class="typ">End</span><span class="pln"> </span><span class="pun">--</span><span class="typ">Finally</span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> tblEmployee table </span><span class="typ">Insert</span><span class="pln"> </span><span class="kwd">into</span><span class="pln"> tblEmployee</span><span class="pun">(</span><span class="typ">Id</span><span class="pun">,</span><span class="pln"> </span><span class="typ">Name</span><span class="pun">,</span><span class="pln"> </span><span class="typ">Gender</span><span class="pun">,</span><span class="pln"> </span><span class="typ">DepartmentId</span><span class="pun">)</span><span class="pln"> </span><span class="typ">Select</span><span class="pln"> </span><span class="typ">Id</span><span class="pun">,</span><span class="pln"> </span><span class="typ">Name</span><span class="pun">,</span><span class="pln"> </span><span class="typ">Gender</span><span class="pun">,</span><span class="pln"> </span><span class="lit">@DeptId</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> inserted </span><span class="typ">End</span><span class="pln"> </span>
Now, let's execute the insert query:
<span class="pln"> </span><span class="typ">Insert</span><span class="pln"> </span><span class="kwd">into</span><span class="pln"> vWEmployeeDetails values</span><span class="pun">(</span><span class="lit">7</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Valarie'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Female'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'IT'</span><span class="pun">)</span><span class="pln"> </span>
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.
No Questions Data Available.
No Program Data.