sqlserver和oracle同步,SQLServer与Oracle的数据同步(触发器trigger)

ALTER proc pro_PublishLastRec_Sql

as

declare @modiid int

declare @signalguid int

declare @areano numeric(1,0)

declare @signalnote varchar(50)

declare @areanote varchar(50)

declare @publishroadstatus varchar(20)

declare @publishtime varchar(50)

if not exists(select * from PublishLastRec_SQL where IsExec=0)

begin

truncate table PublishLastRec_SQL

return

end

declare cur_sql cursor for

select modiid,SignalGUID,AreaNo,SignalNote,AreaNote,PublishRoadStatus,PublishTime from

PublishLastRec_SQL where IsExec=0 order by [id]--IsExec 0为未执行的,1为已执行的

open cur_sql

fetch next from cur_sql into @modiid,@SignalGUID,@AreaNo,@SignalNote,@AreaNote,@PublishRoadStatus,@PublishTime

while @@fetch_status=0

begin

if (@modiid=1) --插入

begin

insert into v_ora_PUBLISHLASTREC(SignalGUID,AreaNo,SignalNote,AreaNote,PublishRoadStatus,PublishTime)

values(@SignalGUID,@AreaNo,@SignalNote,@AreaNote,@PublishRoadStatus,@PublishTime)

end

if (@modiid=2) --删除

begin

delete from v_ora_PUBLISHLASTREC where [email protected] and [email protected]

end

if (@modiid=3) --修改

begin

update v_ora_PUBLISHLASTREC

set [email protected],[email protected],[email protected],

[email protected]

where [email protected] and [email protected]

end

update PublishLastRec_SQL

set IsExec=1

where current of cur_sql

fetch next from cur_sql into @modiid,@SignalGUID,@AreaNo,@SignalNote,@AreaNote,@PublishRoadStatus,@PublishTime

end

deallocate cur_sql