同一SQL 2000服务器上一个数据库中某表更新要同步更新多个数据库的触发器怎么写?

wag_enu 2009-11-06 01:22:06

数据库 master 中有一个的应用软件的账套列表 gaccount ,其中有账套对应的数据库的名称字段其结构和数据是这样的:

并且, 该列表随用户新增或删除账套而增加或删除记录.



order       dbname                              fname                                           

----------- ----------------------------------- -------------------------------------------------- 

1           Try2009a                            账套2009a

2           ab                                  账套ab

3           cd                                  账套cd

4           try2009                             账套try2009



（所影响的行数为 4 行）

每个数据库(dbname列)的结构都完全一样,其中一个货品信息表 goods ,有35个字段,有索引列 goodsid, 和标识列 rec 没有主键;

现在请教一下如何写触发器,实现:

在总账套也就是在数据库 try2009a 中更新(insert ,update, delete) 该 goods 表时,随即更新上面列表中指明的除 try2009a 以外的数据库中对应的 goodsid 表.

...全文

421 16 打赏收藏转发到动态举报

写回复

用AI写文章

16 条回复

切换为时间正序

请发表友善的回复…

发表回复

panlei199242 2011-07-04

打赏
举报

永生天地 2009-11-08

打赏
举报

根据你的问题描述，我建了测试环境，只是goods表里就建了3个字段
写了触发器，解决了你的那两个问题
CREATE TRIGGER tri_goods ON [dbo].[goods]
for INSERT

as

declare @dbname varchar(20)
declare @sql varchar(4000)
declare @err int

declare @goodsid int
declare @rec int
declare @name varchar(50)

begin tran
select @goodsid=goodsid,@rec=rec,@name=name from inserted
--insert into #tmp select goodsid,rec,name from inserted
declare cur_db cursor for select dbname from master..gaccount
open cur_db

FETCH NEXT FROM cur_db INTO @dbname
WHILE @@FETCH_STATUS = 0
begin
if @dbname='Try2009a'
begin
FETCH NEXT FROM cur_db INTO @dbname
continue
end
set @sql = N'insert into '+ @dbname+'.dbo.goods(goodsid,rec,name) values( '+cast(@goodsid as varchar(20))+','+cast(@rec as varchar(20))+','''+@name+''')'
--set @sql = N'insert into '+ @dbname+'.dbo.goods(goodsid,rec,name) values(1,1,''111'' ) '
print @sql
--set IDENTITY_INSERT Try2009a..goods off
execute ('set IDENTITY_INSERT '+@dbname +'.dbo.goods on ' + @sql)
--execute (@sql)
print 'A check constraint violation occurred'
execute ('set IDENTITY_INSERT '+@dbname +'.dbo.goods off')
--exec sp_executesql @sql
if @@error<>0
begin
set @err = 1
print 'A check constraint violation occurred'

break
end
FETCH NEXT FROM cur_db INTO @dbname
end

close cur_db
DEALLOCATE cur_db

if @err=1
begin
delete from goods where goodsid=( select goodsid from goodsid)
rollback tran
end
else
--set IDENTITY_INSERT Try2009a..goods on
--insert into goods(goodsid,rec,name) select goodsid,rec,name from inserted
commit tran

CREATE TRIGGER tri_goods ON [dbo].[goods]
for INSERT, UPDATE, DELETE

as

declare @dbname varchar(20)
declare @sql varchar(4000)
declare @err int

declare @goodsid int
declare @rec int
declare @name varchar(50)

begin tran
select @goodsid=goodsid,@rec=rec,@name=name from inserted
--insert into #tmp select goodsid,rec,name from inserted
declare cur_db cursor for select dbname from master..gaccount
open cur_db

FETCH NEXT FROM cur_db INTO @dbname
WHILE @@FETCH_STATUS = 0
begin
if @dbname='Try2009a'
begin
FETCH NEXT FROM cur_db INTO @dbname
continue
end
set @sql = N'insert into '+ @dbname+'.dbo.goods(goodsid,rec,name) values( '+cast(@goodsid as varchar(20))+','+cast(@rec as varchar(20))+','''+@name+''')'
--set @sql = N'insert into '+ @dbname+'.dbo.goods(goodsid,rec,name) values(1,1,''111'' ) '
print @sql
--set IDENTITY_INSERT Try2009a..goods off
execute ('set IDENTITY_INSERT '+@dbname +'.dbo.goods on ' + @sql)
--execute (@sql)
print 'A check constraint violation occurred'
execute ('set IDENTITY_INSERT '+@dbname +'.dbo.goods off')
--exec sp_executesql @sql
if @@error<>0
begin
set @err = 1
print 'A check constraint violation occurred'

break
end
FETCH NEXT FROM cur_db INTO @dbname
end

close cur_db
DEALLOCATE cur_db

if @err=1
begin
delete from goods where goodsid=( select goodsid from goodsid)
rollback tran
end
else
--set IDENTITY_INSERT Try2009a..goods on
--insert into goods(goodsid,rec,name) select goodsid,rec,name from inserted
commit tran

--insert into goods(goodsid,name) values(3,'3')

--select * from goods

[Quote=引用 7 楼 wag_enu 的回复:]
现在主要有两个问题:
1是不知道如何读取账套列表中的数据库列表和如何根据该数据库循环;
2是不知道如何处理标识列,当insert 时,做了SET IDENTITY_INSERT ab.dbo.goods ON 了还是提示:

仅当使用了列的列表,并且 IDENTITY_INSERT 为 ON 时,才能在表 'ab.dbo.goods' 中为标识列指定显示值.

[/Quote]

bancxc 2009-11-07

打赏
举报

哦帮顶下不会

wag_enu 2009-11-07

打赏
举报

这版的贴子沉得太快了.
再UP一下.

wag_enu 2009-11-06

打赏
举报

大哥大姐们再帮我看看吧.

wag_enu 2009-11-06

打赏
举报

[Quote=引用 7 楼 wag_enu 的回复:]
现在主要有两个问题:
1是不知道如何读取账套列表中的数据库列表和如何根据该数据库循环;
2是不知道如何处理标识列,当insert 时,做了SET IDENTITY_INSERT ab.dbo.goods ON 了还是提示:

仅当使用了列的列表,并且 IDENTITY_INSERT 为 ON 时,才能在表 'ab.dbo.goods' 中为标识列指定显示值.

[/Quote]

谢谢!

xiequan2 2009-11-06

打赏
举报

--新增同步
create trigger tr_insert_A on A
for insert
as
insert B(ID,Name,Address) select ID,Name,Address from inserted
go

--删除同步
create trigger tr_delete_A on A
for delete
as
delete B from deleted d where B.ID=d.ID
go

--更新同步
create trigger tr_update_A on A
for update
as
if update(Name) or update(Address)
update B set Name=i.Name,Address=i.Address
from B,inserted i,deleted d
where i.ID=d.ID and i.ID=b.ID

wag_enu 2009-11-06

打赏
举报

谢谢各位.

wag_enu 2009-11-06

打赏
举报

现在主要有两个问题:
1是不知道如何读取账套列表中的数据库列表和如何根据该数据库循环;
2是不知道如何处理标识列,当insert 时,做了SET IDENTITY_INSERT ab.dbo.goods ON 了还是提示:

仅当使用了列的列表,并且 IDENTITY_INSERT 为 ON 时,才能在表 'ab.dbo.goods' 中为标识列指定显示值.