How to create Update Trigger Instead of Trigger Part 2

Sagar Jaybhay

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.

Update Trigger

When we create an update trigger we get both magic tables that are inserted and deleted table in this.

After Update Trigger
After Update Trigger

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.

Insted Of Trigger 1
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.

Insert Data Using View

GitHub: https://github.com/Sagar-Jaybhay

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.

Related posts