Instead Of Triggers

In this blog, you will learn about instead of triggers.

Instead of triggers are used to skip DML commands. They fire when you try to execute insert, update or delete statement but instead of executing these commands trigger actually works and trigger functionality executes.

 

Example 

create table approved_emp ( eid int identity(1,1), ename varchar(30))
create table emp ( id int identity(1,1) , ename varchar(30), AddedBy varchar(30))

Create trigger instead_of on approved_emp
instead of insert
as
begin
declare @name varchar(30)
select @name=ename from inserted
insert into temp_audit values(@name, USER )
end

So, basically, trigger will work as, when we will try to add new record in approved_emp table, instead of inserting new records it will add ename into emp table. No data will reflect in approved_emp table as trigger is fired on the table every time while adding data into that table.

 

You can also create instead of triggers for update and delete as well.

 

Here is a complete tutorial: Triggers In SQL Server