create trigger yourTrigger on yourTable
For update
As
declare ins cursor for Select * from inserted
declare del cursor for Select * from deleted
open ins
fetch next from ins into @...
open del
fetch next from del into @...
while @@fetch_status = 0
begin
处理语句
fetch next from ins into @...
fetch next from del into ...
end
close ins
deallocate ins
deallocate del
For update
As
declare ins cursor for Select * from inserted
declare del cursor for Select * from deleted
open ins
fetch next from ins into @...
open del
fetch next from del into @...
while @@fetch_status = 0
begin
处理语句
fetch next from ins into @...
fetch next from del into ...
end
close ins
deallocate ins
deallocate del
For update
As
declare ins cursor for Select * from inserted
declare del cursor for Select * from deleted
open ins
fetch next from ins into @...
open del
fetch next from del into @...
while @@fetch_status = 0
begin
处理语句
fetch next from ins into @...
fetch next from del into ...
end
close ins
deallocate ins
deallocate del
create trigger t_update
for update
as
declare @id1 int,@id2 int --被更新前/后的主键--定义游标
declare c_i cursor for select id from inserted
declare c_d cursor for select id from deleted--打开游标
open c_i
open c_d--从游标中循环取数,并进行相关的处理
fetch next from c_i into @id1
while @@fetch_status=0
begin
fetch next from c_d into @id2
update 表 set id=id1 where id=id2 --数据处理语句
fetch next from c_i into @id1
fetch next from c_d into @id2
end--关闭游标
close c_i
close c_d--释放游标
deallocate c_i
deallocate c_d
go
避免多条同时触发
For update
As
declare @ins_value as valuetype ,@del_value as valuetype declare ins_cur cursor local for Select value from inserted
declare del_cur cursor local for Select value from deleted
open ins_cur
fetch next from ins into @ins_value
open del_cur
fetch next from del into @del_value
while @@fetch_status = 0 begin
//处理
fetch next from ins into @ins_value
fetch next from del into @del_value
end
close ins_cur
close del_cur
deallocate ins_cur
deallocate del_cur
declare ins_cur cursor local for Select value from inserted,表名
where 表名.id=inserted.id declare del_cur cursor local for Select value from deleted,表名
where 表名.id=inserted.id
create trigger 名 on 表
for update
as
insert 目标表 (列1,列2) select 列1,列2 from inserted
1.没有唯一不可变字段的情况下,可以使用txlicenhe(马可@李),Sorder(剑客)两位朋友的方法
2.有唯一且不可变字段的情况下,可以使用 zjcxc(邹建)朋友的方法,只是不需要建两个@id变量,有一个就够了。