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.