How to create Update Trigger Instead of Trigger Part 2
In Previous article we understand what is trigger and how to create trigger. Previous article link. In this article we understand What is Update Trigger?. How to Create it in SQL Server By Sagar Jaybhay.
We will understand How to create Insted Of Trigger and what is use of that in SQL Server.
When we create an update trigger we get both magic tables that are inserted and deleted table in this.
See above image we get inserted table and deleted table and if we select both these tables we will find that the inserted table gets new values or updated data and deleted table contains an old value which is previously stored in that table.
create trigger afterupdation on studenttable for update as begin select * from inserted; select * from deleted; end; --delete from studentTable where id=7; update studenttable set first_name='Sagaraa' , last_name='Jaybhahy' where id=7;
Instead of Trigger
This Trigger is fired on DML actions like Insert, update and Delete and this will fire when Instead of triggering action.
This triggers are generally used to update multiple base tables correctly.
In the previous article of view, you will understand if our view contains multiple tables and if we fire the insert statement and both these tables contain a common field then It will throw an error so avoid this we can use instead of trigger.
To understand well we create a view below tables as shown in the figure.
create view vw_empdec as select e.id,e.name,e.salary,d.deptname from tblEmp as e join tbldept as d on e.deptid=d.deptid;
Now we create the above view on this table and the query is shown above.
After this, we have are trying to insert records in this but we get some error.
insert into vw_empdec values(2,XYZ',2222,'IT');
Msg 4405, Level 16, State 1, Line 19
View or function ‘vw_empdec’ is not updatable because the modification affects multiple base tables.
Now to fix this we will create an Instead of Trigger
create trigger tr_instedoftblemp on vw_empdec Instead of insert as begin declare @id int declare @deptid int select @deptid=deptid from tbldept join inserted on inserted.deptname=tbldept.deptname if(@deptid is null) begin Raiserror('Departname is present',16,1) return end insert into tblEmp(id,name,salary,deptid) select id,name,salary,@deptid from inserted end;
After this, we fire insert statements and we able to insert data into a table. In the above code, we use Raiserror function if the departname is not present then we cant get deptid and for that, we raise an error.
Sagar Jaybhay, from Maharashtra, India, is currently a Senior Software Developer. He has continuously grown in the roles that he has held in the more than seven years he has been with this company. Sagar Jaybhay is an excellent team member and prides himself on his work contributions to his team and company as a whole.