创建链接数据库方式的步骤在这里不重复说明,很多地方都有资料!
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
