触发器实现两个库之间数据同步

触发器实现两个库之间数据同步

 

instead of触发器不执行原语句，那么在原来基础上再加一条insert语句即可。

 

for触发器不能有text,ntext,image列。

 

 

 

create trigger tgr_nr_add

on Nr

instead of insert

as

--declare @name varchar(50),@name1 varchar(200);

begin

SET NOCOUNT ON;

set xact_abort ON;

--set @name=

begin  distributed   tran

if (select sp from inserted)='1'

--begin

insert into opendatasource('SQLOLEDB','Data Source=10.10.100.202;User ID=sq_fjrtvu;Password=8v6455dfw').sq_fjrtvu.dbo.Module_News

(NewsTitle ,NewsContent,Author,PubTime,ClassId,IsTop)

 select Subject,Content,gar,New_Date,11,Zd from inserted

 

INSERT INTO [Nr]

           ([sid]

           ,[ssid]

           ,[subject]

           ,[content]

           ,[picname]

           ,[waddr]

           ,[link01]

           ,[link02]

           ,[gar]

           ,[new_date]

           ,[sp]

           ,[pic2]

           ,[pic3]

           ,[pic4]

           ,[pic5]

           ,[zw]

           ,[zd]

           ,[bt]

           ,[userip]

           ,[publ_time]

           ,[publ_login]

            ,[tp])

select [sid]

           ,[ssid]

           ,[subject]

           ,[content]

           ,[picname]

           ,[waddr]

           ,[link01]

           ,[link02]

           ,[gar]

           ,[new_date]

           ,[sp]

           ,[pic2]

           ,[pic3]

           ,[pic4]

           ,[pic5]

           ,[zw]

           ,[zd]

           ,[bt]

           ,[userip]

           ,[publ_time]

           ,[publ_login]

            ,[tp]

from inserted

commit tran

 

--end

end