DML Instead Of در SQL یک نوع trigger است و قبل از آنکه SQL Server عملیاتی که باعث اجرای آن شده، اجرا می شود. در ادامه مثال هایی از این نوع Trigger را مشاهده خواهید کرد و یا نحوه استفاده و بکارگیری آن آشنا خواهید شد.


Syntax ایجاد Instead of Trigger بصورت زیر می باشد.

CREATE TRIGGER triggername



ON { tablename | viewname }



INSTEAD OF {[INSERT] [,] [UPDATE] [,] [DELETE] }



AS



{

 ---sqlstatements

 }

حال اجازه دهید به مثال هایی از Instead Of Trigger بپردازیم.
فرض کنید که نیاز است ردیف ها یا رکوردهایی از View حذف شوند ولی در اصل نباید حذف شوند و فقط باید رکوردهایی که قرار است حذف شوند، Track شوند و رکوردها سر جای خود باقی می مانند.
برای این منظور به تریگر INSTEAD OF DELETE نیاز داریم.
پس ابتدا یک View ایجاد می کنیم.

Create VIEW Vw_OrderDetails

as

select OrderId ,ProductId ,UnitPrice, Quantity, Discount

from

[Order Details]

حالا یک Instead of trigger برای این 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

حالا عملکرد Instead of تریگری که ایجاد کرده ایم را بررسی خواهیم کرد. در واقع درصدد حذف رکوردی که در View، orderId =10248 می باشد را داریم. در این شرایط باید تریگری که ایجاد کردیم، وارد عمل شود.

delete from Vw_OrderDetails where OrderId =10248

همانطور که انتظار می رفت، این تریگر وارد کار شده و به جای حذف رکورد برای رکوردی که دارای orderId =10248 بود، فقط یک فیلد را در جدول مربوطه بروزرسانی کرد. فیلد Is_deleted در جدول مربوط به عدد یک تغییر کرد و عملیات حذف رخ نداد.
بنابراین ردیابی و Track کردن رکوردهایی که سعی به حذف آنها شده، ساده می شود.

select * from Vw_OrderDetails where OrderId =10248

select * from [Order Details] where OrderId =10248

در ادامه یک View ایجاد خواهیم کرد که از Join چند جدول تشکل شده است.

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

اکنون اگر درصدد بروزرسانی رکوردی در View باشید، با خطا مواجه خواهید شد:

Update Vw_Code SET Code ='RTD6' , Full_code = 'RTD6702' WHERE SNO =1

برای جلوگیری از خطا، می توانید تریگر Instead of ایجاد کنید و تا از ایجاد خطا جلوگیری کرده و تغییرات را به جای View به جداول مربوطه ارجاع دهد.

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

اکنون دستور Update، به جای آپدیت در View، رکوردها را در جداول مربوطه ویرایش می کند.

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