可用分押宝游戏火热进行中...	专题改版：Java Web 专题

批量更新触发器

楼主firefly_xu（萤火虫）2003-12-04 16:45:24 在 MS-SQL Server / 基础类提问

请问一下UPDATE触发器能否实现批量更新？
“
CREATE TRIGGER [equpdate] ON [dbo].[equipment]
FOR UPDATE
AS

declare @oldbh char(10),@newbh char(10)

declare new cursor local for select num from inserted

open new

fetch next from new into @newbh
while @@fetch_status=0
begin

declare old cursor local for select num from deleted
open old
fetch next from old into @oldbh
while @@fetch_status=0
begin
update server_t set num=@newbh where @oldbh!=@newbh
fetch next from old into @oldbh
end

close old
deallocate old

fetch next from new into @newbh
end

close new
deallocate new
”
如有问题请高手指正，谢谢！！！问题点数：100、回复次数：10Top

1 楼zjcxc（邹建）回复于 2003-12-04 16:52:17 得分 50

--不能.要改为:
CREATE TRIGGER [equpdate] ON [dbo].[equipment]
FOR UPDATE
AS
declare @oldbh char(10),@newbh char(10)

declare new cursor local for select num from inserted
declare old cursor local for select num from deleted

open new
open old

fetch next from new into @newbh
fetch next from old into @oldbh
while @@fetch_status=0
begin
update server_t set num=@newbh where @oldbh!=@newbh
fetch next from new into @newbh
fetch next from old into @oldbh
end

close old
close new
deallocate new
deallocate old
goTop

2 楼zjcxc（邹建）回复于 2003-12-04 16:55:56 得分 0

--上面这个可以实现批量修改,但按照我的理解,应该是这样做才对:
CREATE TRIGGER [equpdate] ON [dbo].[equipment]
FOR UPDATE
AS
declare @oldbh char(10),@newbh char(10)

declare new cursor local for select num from inserted
declare old cursor local for select num from deleted

open new
open old

fetch next from new into @newbh
fetch next from old into @oldbh
while @@fetch_status=0
begin
if @oldbh<>@newbh update server_t set num=@newbh where num=@oldbh
fetch next from new into @newbh
fetch next from old into @oldbh
end

close old
close new
deallocate new
deallocate old
go
Top

3 楼zjcxc（邹建）回复于 2003-12-04 17:00:17 得分 0

--下面这个方法更简单:

CREATE TRIGGER [equpdate] ON [dbo].[equipment]
FOR UPDATE
AS
select id=identity(int,1,1),num into #t1 from inserted
select id=identity(int,1,1),num into #t2 from deleted

update server_t set num=b.n_num
from server_t a join(
select n_num=a.num,o_num=b.num
from #t1 a join #t2 b on a.id=b.id
where a.num<>b.num) b on a.num=b.o_num
go

Top

4 楼firefly_xu（萤火虫）回复于 2003-12-04 17:33:01 得分 0

注意：
equipment.num是主关键字有唯一的值，
server_t.num不是关键字是有重复的值的。
它们是一对多的关系。
Top

5 楼pengdali（）回复于 2003-12-04 17:41:38 得分 0

企业管理器--右键表--设计表--任意字段处按右键--关系--新建--设置与其他表的关联(即那些表的字段与本表的字段有关系,要被自动更新)--设置好后,选中"对 INSERT 和 UPDATE 强制关系"--并同时选中"级联更新相关的字段",如果要级联删除,再选中"级联删除相关的记录"--最后确定就可以了
Top

6 楼pengdali（）回复于 2003-12-04 17:43:55 得分 0

CREATE TRIGGER [equpdate] ON [dbo].[equipment]
FOR UPDATE
AS
if (select count(*) from inserted)>1
rollback tran

update server_t set num=(select num from inserted) where num=(select num from deleted)
goTop

7 楼pengdali（）回复于 2003-12-04 17:44:27 得分 30

sqlserver 2000用上面的

sqlserver 7.0用下面的Top

8 楼teaism（）回复于 2003-12-04 17:46:20 得分 0

你在equipment加一个identity列
alter table equipment add id int identity

然后:
CREATE TRIGGER [equpdate] ON [dbo].[equipment]
FOR UPDATE
AS
update A set num=B.num from server_t A,inserted B,deleted C
where B.id=C.id and A.num=C.num and B.num<>C.numTop

9 楼teaism（）回复于 2003-12-04 17:47:58 得分 20

加一个identity列就可以批量更新了.Top

10 楼azsoft（Try my best）回复于 2003-12-04 21:13:40 得分 0

大力说过：不用游标也同样是可以解决的。Top