創建鏈接數據庫方式的步驟在這里不重復說明,很多地方都有資料!
CREATE TRIGGER TransferMTMessage ON [dbo].[T_DWS_MT_Message]
FOR INSERT
AS
-- 必須設置這個選項目,否則出現 OLE DB 錯誤跟蹤
--［OLE/DB PRovider 'MSDAORA' ITransactionLocal::StartTransaction returned 0x8004d013: ISOLEVEL=4096
--解決異構服務器的觸發器 參考:http://support.microsoft.com/default.aspx?scid=kb;EN-US;280106
SET XACT_ABORT ON
Declare @Seq int
Declare @LinkID varchar(20)
Declare @Content varchar(140)
Declare @Mobile varchar(20)
--Step1: 從Oracle數據庫獲取一個序列的nextval
Select @Seq=(Select * from openquery(hnoracle,'Select Seq.nextval From dual'))
--Step2: 獲取新插入的數據
Select @LinkID=LinkID From INSERTED
Select @Content=SMS_Content From INSERTED
Select @Mobile=MT_Mobile From INSERTED
--Step3:將數據通過鏈接數據庫寫進Oracle數據庫
INSERT INTO [hnoracle]..[HAILINE].[MTMESSAGE](MTMSGID,MTMOBILE,CONTENT,LINKID,STATUS,SENDTIME,SPFLAG)
Values(@Seq, @Mobile, @Content, @LinkID,0,NULL,NULL)
--Step4:刪除本地SQLServer下行信息
Delete From T_DWS_MT_Message Where ID IN( Select ID From INSERTED)
Return
