Hello and welcome, today let us discuss about how to use instead of triggers in SQL Server 2012. The main advantage of Instead 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.
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.
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
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’)
Now, let us check our base tables.
SELECT * FROM EXEC_APPLICANT SELECT * FROM EXEC_EMPLOYEE