Home » SQL DML Instead Of Trigger

SQL DML Instead Of Trigger

Instead of Trigger (using FOR/AFTER CLAUSE)


This type of trigger 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 we have a requirement that when any rows are tried to delete through the view,  just keep the track of those rows but rows should not actually be deleted.

For this requirement we can create the following INSTEAD OF DELETE trigger.

Following statement creates a View of Order Details table.

Create VIEW Vw_OrderDetails
as
select OrderId ,ProductId ,UnitPrice, Quantity, Discount
from
[Order Details]
Lest create an Instead of trigger on View.
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

Lets check the implementation of an Instead of trigger to keep the track of rows when rows are tried to delete from viwes, here we delete rows from view for orderId =10248 .

delete from Vw_OrderDetails where OrderId =10248

You can see the below output of View and base table , When orderId =10248 is deleted from view, an Instead of trigger is called that skip the delete event so the rows are not deleted from view and update the flag is_deleted =1 in base table for this record .

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 an 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

Must read ..

After Trigger (using FOR/AFTER CLAUSE)

DDL TRIGGER

LOGON Trigger

 

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.