曾经以为SQL SERVER的触发器只能触发单行,也就是说如果一个delete触发器,如果同时删除多行时,只会对第一条记录触发,后来发现了不是人家SQL SERVER不支持,而是偶脑子笨没发现。
其实inserted和deleted两张内部表存放了所有要插入或要删除的记录,可以用cursor逐次访问里面的每条记录,下面是一个示例,该触发器将要删除的记录转移到另一张表中:
第一步,创建这两张表
create table table1([id] int primary key, [value] varchar(100))
create table table2([id] int primary key, [value] varchar(100))
第二步,插入测试数据
declare @i int
set @i = 1
while @i <= 100
begin
insert into table1([id], [value])
values(@i, cast(@i as varchar))
set @i = @i + 1
end
创建table1的delete触发器
create trigger tr_d_table1 on table1 for delete
as
begin
declare @id int, @value varchar(100)
declare cur_del cursor local forward_only for
select [id], [value]
from deleted
open cur_del
fetch next from cur_del into @id, @value
while @@fetch_status = 0
begin
insert into table2([id], [value])
values(@id, @value)
![]()
fetch next from cur_del into @id, @value
end
close cur_del
deallocate cur_del
![]()
end
现在对table1执行delete语句,发现所有被删除的记录都记录在在table2中了
