oracle触发器写数据到sql,在SQLServer2000中用链接数据库方式向Oracle8.17写记录的Trigger-数据库专栏,ORACLE...

创建链接数据库方式的步骤在这里不重复说明,很多地方都有资料!

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