Home » SQL DML Instead Of Trigger

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]
[ Also Read: LOGON Trigger]
After creating a view, Lets create an Instead of trigger named tr_Vw_OrderDetails on View, that will be fired when delete command is executed on View and skips the delete event and update the Is_Deleted flag value =1 against the records in table [Order Details] those are tried to delete.




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)

DDL TRIGGER

LOGON Trigger




SQL Basics TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window
....More....More....More

 791 total views,  6 views today

Leave a Reply

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