SQL DML Instead Of Trigger

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

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)

DDL TRIGGER

LOGON Trigge