Question

SQL Server 2008 同一表下同一事件有两个触发器，其中一个对另一个产生影响导致数据无法删除

第一个触发器是：
ALTER TRIGGER test
ON H_K_B
for DELETE
AS
declare @x varchar(20),@id varchar(20)

select @x = x, @id = id from deleted
declare @istates varchar(10)
select @istates = istates from A where x = @x

if @istates = 2 and not exists(select 1 from B where x=@x istates<3)
begin
return
end
else
begin
delete c from test c inner join deleted b on 1=1 and a.id=b.id
end

然后我想再加一个触发器：
alter trigger new_delete
on H_K_B
for delete
as
insert into tbl_new
select deleted.id,deleted.x,deleted.k_id from deleted left join D on D.x=deleted.x and D.k_id=deleted.k_id where D.sf=1
然后第二个触发器就无法从表H_K_B中删除数据了，表tbl_new中也没有插入数据，用其他的表测试没有问题，然后把第一个触发器禁用也没问题，就是同时都启用两个的时候第二个触发器就没反应，这是为什么

Answer 1

你把这两个触发器写在一个触发器里面吧
alter trigger new_delete
on H_K_B
for delete
as
insert into tbl_new
select deleted.id,deleted.x,deleted.k_id from deleted left join D on D.x=deleted.x and D.k_id=deleted.k_id where D.sf=1
declare @x varchar(20),@id varchar(20)

select @x = x, @id = id from deleted
declare @istates varchar(10)
select @istates = istates from A where x = @x

if @istates = 2 and not exists(select 1 from B where x=@x istates<3)
begin
return
end
else
begin
delete c from test c inner join deleted b on 1=1 and a.id=b.id
end

追问

对，我后来就是这么写的，然后就出现了tbl_new里有插入的数据，但是删除的数据还在原表里，这是为什么