How to use instead of triggers in SQL Server 2012

Hello and welcome, today let us discuss about how to use instead of triggers. Recently, when working on a site based on Magento CMS and that uses magento plugins, I had some fun with Instead. The main advantage of Instead in SQL Server 2012 of trigger are they support INSERT, UPDATE and DELETE the referenced tables which are used in views. We mainly instead of triggers are used in views to perform DML operations. Syntax is same as After triggers, If you did not read about. I will be using this view to explain you instead of triggers. well, lets start exploring on Instead of triggers.

Instead of trigger on a View in SQL Server 2012

As we are creating an Instead of trigger on a View, the Instead of trigger exists in DB_Name > views > View_name > Triggers. Follow the image below.

1

In the example here we have used tow different tables named EXEC_APPLIACNT and EXEC_EMPLOYEE to create a view and now we shall design our Instead of triggers such that you can apply all DML operations (INSERT,UPDATE,DELETE) on the view. If you directly try to insert the data in to the view which has two or more tables in it, you will get the following error:

Msg 4405, Level 16, State 1, Line 1
View or function ‘view_name’ is not updatable because the modification affects multiple base tables. In order to fix it you need to use Instead of triggers.

Designing INSTEAD OF Triggers for Views

We have two base tables in the view, which means you need to insert the data in two different tables so we will be having two different insert statements in our trigger.

Following is the pictorial explanation of syntax. Note I am only writing INSTEAD OF Trigger for INSERT you can mention UPDATE and DELETE by mentioning comma (,) as separator.

2

Just a simple select statements between Begin and END

CREATE TRIGGER [dbo].[TRG_VM_EMPDETAILS]
ON [dbo].[VW_EMPDETAILS]
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO EXEC_APPLICANT (APP_FNAME,APPLICANT_MARITALSTATUS,APPLICANT_TYPE)
SELECT [NAME],APPLICANT_MARITALSTATUS,APPLICANT_TYPE FROM INSERTED

INSERT INTO EXEC_EMPLOYEE (EMP_DOJ,EMP_NOTICEPERIOD)
SELECT [DATE OF JOIN], [NOTICE PERIOD] FROM INSERTED

END

3

Execution :

Let’s us insert a record in a view and see if base tables are effected.

INSERT INTO VW_EMPDETAILS VALUES
(‘EXECSQL.ORG’,’2014-08-13′,’10′,‘AB+’,‘Single’,‘Permanent’)

4

Now, let us check our base tables.

SELECT * FROM EXEC_APPLICANT
SELECT * FROM EXEC_EMPLOYEE

5

Leave a Reply

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