现有两个库中的两张表a,b字段完全相同,表名也一样,但是是在不同的数据库中,projectid号是关键字。
我需要在a表插入一条记录的时候将这条记录同时触发到另一个数据库中的b表中。但是我为了标示是从a表所在的库中触发过来的项目,将触发到b中的projectid号前面加个标示。
比如在a中的projectid='0001',触发到b中的projectid应该为projectid='a00001'
我写了一个触发,但是没修改过来。请指教:
CREATE TRIGGER project_tr
ON dbo.project
for insert,delete
AS
begin
set XACT_ABORT on
declare @sn_del varchar(20),@sn_ins varchar(20),@cun as int
declare @fun varchar(20)
select @sn_del=projectid from deleted del
select @sn_ins=projectid from inserted ins
select @fun='jm' + @sn_ins
select @sn_del='jm' + @sn_delselect @cun=count(*) from opendatasource('SQLOLEDB','Data Source=zlw;User ID=sa;Password=').yiyaonew.dbo.project where projectid=@sn_insif @cun>0
begin
delete opendatasource('SQLOLEDB','Data Source=zlw;User ID=sa;Password=').yiyaonew2.dbo.project where projectid=@sn_ins
insert into opendatasource('SQLOLEDB','Data Source=zlw;User ID=sa;Password=').yiyaonew2.dbo.project select * from project where projectid=@sn_ins
update opendatasource('SQLOLEDB','Data Source=zlw;User ID=sa;Password=').yiyaonew2.dbo.project set projectid=@fun where projectid=@sn_ins
end
elsebegin
delete opendatasource('SQLOLEDB','Data Source=zlw;User ID=sa;Password=').yiyaonew2.dbo.project where projectid=@sn_del
insert into opendatasource('SQLOLEDB','Data Source=zlw;User ID=sa;Password=').yiyaonew2.dbo.project select * from project where projectid=@sn_ins
update opendatasource('SQLOLEDB','Data Source=zlw;User ID=sa;Password=').yiyaonew2.dbo.project set projectid=@fun where projectid=@sn_ins endend
我需要在a表插入一条记录的时候将这条记录同时触发到另一个数据库中的b表中。但是我为了标示是从a表所在的库中触发过来的项目,将触发到b中的projectid号前面加个标示。
比如在a中的projectid='0001',触发到b中的projectid应该为projectid='a00001'
我写了一个触发,但是没修改过来。请指教:
CREATE TRIGGER project_tr
ON dbo.project
for insert,delete
AS
begin
set XACT_ABORT on
declare @sn_del varchar(20),@sn_ins varchar(20),@cun as int
declare @fun varchar(20)
select @sn_del=projectid from deleted del
select @sn_ins=projectid from inserted ins
select @fun='jm' + @sn_ins
select @sn_del='jm' + @sn_delselect @cun=count(*) from opendatasource('SQLOLEDB','Data Source=zlw;User ID=sa;Password=').yiyaonew.dbo.project where projectid=@sn_insif @cun>0
begin
delete opendatasource('SQLOLEDB','Data Source=zlw;User ID=sa;Password=').yiyaonew2.dbo.project where projectid=@sn_ins
insert into opendatasource('SQLOLEDB','Data Source=zlw;User ID=sa;Password=').yiyaonew2.dbo.project select * from project where projectid=@sn_ins
update opendatasource('SQLOLEDB','Data Source=zlw;User ID=sa;Password=').yiyaonew2.dbo.project set projectid=@fun where projectid=@sn_ins
end
elsebegin
delete opendatasource('SQLOLEDB','Data Source=zlw;User ID=sa;Password=').yiyaonew2.dbo.project where projectid=@sn_del
insert into opendatasource('SQLOLEDB','Data Source=zlw;User ID=sa;Password=').yiyaonew2.dbo.project select * from project where projectid=@sn_ins
update opendatasource('SQLOLEDB','Data Source=zlw;User ID=sa;Password=').yiyaonew2.dbo.project set projectid=@fun where projectid=@sn_ins endend
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货