为什么批量删除记录时触发器只执行最后一条

[quansui8]

有两个表a和b
当在表a上执行delete from a
实际删除了3条记录
触发器只更新了表b的最后一条
示例：
--创建测试环境
create table a
(
id int primary key,
name varchar(20)
)

declare @sql nvarchar(200)
set @sql=N' insert a select 2,''BBBBBB'''+
N' insert a select 3,''CCCCCC'''+
N' insert a select 4,''DDDDDD'''
--select @sql
exec(@sql)
create table b
(
id int primary key,
sex varchar(10)
)
go

insert into b
select 1,'A'
union all select 2,'A'
union all select 3,'A'
union all select 4,'A'

--创建触发器
create trigger a_to_b
on a
for delete
as
declare @iid int
select @iid= id from deleted
update b set Sex='d'
where id=@iid

go

--执行以下语句
delete from a

--查询结果
select * from a
select * from b

--删除测试环境
drop table a
drop table b

[lh1979]

select @iid= id from deleted
update b set Sex='d'
where id=@iid
你赋值的变量获得的值是付给记录的最后一个值,当然只删除一个了

[lh1979]

可以这样
update b set Sex='d'
where id in (select id from deleted)

[wangdehao]

触发器的问题
create trigger a_to_b
on a
for delete
as
declare @iid int
update b set Sex='d'
where id in(select id from deleted)
----这是deleted的表里是有多条记录,所以你的触发器就出现问题了

[jiang130]

建議改為
create trigger a_to_b
on a
for delete
as
update b set Sex=a.id from b inner join deleted a on b.id=a.id

[631799]

或在触发器里发游标：

--创建触发器
alter trigger a_to_b
on a
for delete
as
declare @iid int
declare #c cursor for select id from deleted
open #c
fetch next from #c into @iid
while @@fetch_status=0
begin
update b set Sex='d'
where id=@iid
fetch next from #c into @iid
end

go