創建鏈接數據庫方式的步驟在這里不重復說明,很多地方都有資料!
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
