Instead of Trigger (using FOR/AFTER CLAUSE) fires before SQL Server starts the execution of the action that fired it.
INSTEAD OF triggers cause their source DML operation to skip and they just execute the code provided inside them.
SYNTAX
CREATE TRIGGER triggername ONย { tablenameย |ย viewnameย } INSTEAD OF {[INSERT] [,] [UPDATE] [,] [DELETE] } AS { ---sqlstatements }

Lets look at an example of Instead Of Trigger in SQL Server.
Suppose you have a requirement that when someone try to delete any row through SQL View, then in that case just keep the track of those rows but the actual rows should not get deleted from table.
For this requirement you can create the INSTEAD OF DELETE trigger.
First we will create a View named Vw_OrderDetails, following statement creates a View of Order Details table.
Create VIEW Vw_OrderDetails as select OrderId ,ProductId ,UnitPrice, Quantity, Discount from [Order Details]

CREATE TRIGGER tr_Vw_OrderDetails ON Vw_OrderDetails INSTEAD OF DELETE AS BEGIN update [Order Details] set Is_deleted = 1 where OrderId in (select OrderId from deleted) END
Now time to checking the implementation of an Instead of trigger to keep the track of rows when someone try to delete any record from from views.
ย Lets delete the records from view for orderId =10248.
delete from Vw_OrderDetails where OrderId =10248
You can see the below output of View Vw_OrderDetails and base table [Order Details], when records for orderId =10248 are deleted from view, an Instead of trigger tr_Vw_OrderDetails is called that skip the delete event so the rows are not deleted from view and is_deleted is updated to 1 in base table for these records.
So it will be easy to keep the track of those records which are tried to delete through view.
select * from Vw_OrderDetails where OrderId =10248 select * from [Order Details] where OrderId =10248
Instead of Trigger for Update
In case, when you have joined multiple tables to create a view as given below.
CREATE VIEW vw_code AS SELECT A.SNO ,A.CODE ,B.FULL_CODE FROM TBL1 A INNER JOIN tbl2 B ON A.SNO =B.SNO
Now when you try to update the View, it returns an error as given below.
Update Vw_Code SET Code ='RTD6' , Full_code = 'RTD6702' WHERE SNO =1
To avoid such error and make a view modifiable, you need to create an Instead of trigger on the view , that will skip the changes on view and pass the changes to base tables.
CREATE TRIGGER tgr_Vw_code ON Vw_Code INSTEAD OF UPDATE AS BEGIN DECLARE @Code as varchar(5),@fullcode as varchar(10) DECLARE @SNO as INT SET @Code = (select code from inserted) SET @fullcode = (select full_code from inserted) SET @SNO =(select sno from inserted) UPDATE TBL1 SET CODE = @Code WHERE SNO =@SNO UPDATE TBL2 SET FULL_CODE = @fullCode WHERE SNO =@SNO END
Now you can execute update statement against the view and it will update the data to the base tables.
Update Vw_Code SET Code ='CBVH' , Full_code = 'CBVH945' WHERE SNO =1
Select * from tbl1 where sno =1 select * from tbl2 where sno =1 select * from vw_Code where sno =1
Also Read..
After Trigger (using FOR/AFTER CLAUSE)
2,232 total views, 4 views today