2012年11月14日 星期三

MSSQL Trigger to run External program

Reference:
http://www.blueshop.com.tw/board/show.asp?subcde=BRD20080516112642UCO

1. Create Trigger on Table

2. Trigger Example: run external program C:\tmp\test.pl when column xserver_name is 'IBM x336'.


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE TRIGGER My_Trigger_Name
   ON  master.dbo.spt_fallback_db
   AFTER INSERT,DELETE,UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
if(SELECT xserver_name FROM inserted) = 'IBM x336'
BEGIN
EXEC master..xp_cmdshell 'cmd.exe /C "C:\tmp\test.pl "'
END
END
GO

--PS. If the external program with parameters, reference this document.

3. Enable MSSQL to run external program


2012/11/15 update:
把 trigger 放到實際上線的系統後發現,trigger 執行過程會把這一筆資料 lock 住,結果造成原本程式的後半段無法更新資料。

沒有留言:

張貼留言