Question

SQL SERVER触发器级联删除时出错

数据库里有三张表，category，news，comment，其中news的caid和category中的主键id有外键关联，comment的newsid和news的主键id有外键关系。因此我写了两个触发器，category的触发器代码如下：
ALTER TRIGGER [dbo].[trigCategoryDelete]
ON [dbo].[category]
INSTEAD OF delete
AS
BEGIN
declare @caId int
select @caId = id from deleted
delete comment where newsId in (select id from news where caId = @caId)
delete news where caId = @caId
delete category where id = @caId
END
news表的触发器代码如下：
ALTER TRIGGER [dbo].[trigNewsDelete]
ON [dbo].[news]
INSTEAD OF delete
AS
BEGIN
declare @id int
select @id = id from deleted
delete comment where newsId = @id
delete news where id = @id
END
现在的问题是category的触发器执行delete语句时会报错，此时禁用news的触发器就正常了。请问这是为什么？news表的触发器是不是对category表的触发器产生了干扰？我怀疑是因为category表的触发器中对news表执行delete语句时，调用了news表的触发器。如果真是这样，为什么我把category表的触发器中delete comment where newsId in (select id from news where caId = @caId)这句话删除，还是会出错？请高手帮我分析下这个问题。

Answer 1

ALTER TRIGGER [dbo].[trigCategoryDelete]
ON [dbo].[category]
INSTEAD OF delete
AS
BEGIN
delete comment where newsId in (select id from news where exists(select id from deleted where deleted.id=news.caId))
delete news where exists(select id from deleted where deleted.id=news.caId)
delete category where exists(select * from deleted where deleted.id=category.id)
END

news表的触发器代码如下：
ALTER TRIGGER [dbo].[trigNewsDelete]
ON [dbo].[news]
INSTEAD OF delete
AS
BEGIN
delete comment where exists(select * from deleted where deleted.id=comment.newsId)
delete news where exists(select * from deleted where deleted.id=news.id)
END

/*
利用exists进行deleted表和原表对比
如果用户执行:delete from 表 where id>100的批删除
楼主的就会出错
如果确定是一行处理,请使用:
ALTER TRIGGER [dbo].[trigNewsDelete]
ON [dbo].[news]
INSTEAD OF delete
AS
if @@rowcount=1
BEGIN

.........代码........
END
*/

Answer 2

很奇怪啊, 我这里按照你的样子, 执行了一下,一切正常啊

CREATE TABLE category (
id int primary key,
val int
);
go

CREATE TABLE news (
id int primary key,
caid int,
val int,
FOREIGN KEY (caid) REFERENCES category
);
go

CREATE TABLE comment (
id int primary key,
newsid int ,
val int,
FOREIGN KEY (newsid) REFERENCES news
);
go

上面模拟建了3张表, 以及 外键.

创建了2个触发器

create TRIGGER [dbo].[trigCategoryDelete]
ON [dbo].[category]
INSTEAD OF delete
AS
BEGIN
declare @caId int
select @caId = id from deleted
delete comment where newsId in (select id from news where caId = @caId)
delete news where caId = @caId
delete category where id = @caId
END
go

create TRIGGER [dbo].[trigNewsDelete]
ON [dbo].[news]
INSTEAD OF delete
AS
BEGIN
declare @id int
select @id = id from deleted
delete comment where newsId = @id
delete news where id = @id
END
go

模拟插入点数据.
1>
2> INSERT INTO category VALUES(1, 1);
3> INSERT INTO news VALUES(1, 1, 1);
4> INSERT INTO comment VALUES(1, 1, 1);
5> GO

(1 行受影响)
1>
2> delete from category where id = 1
3> go

(1 行受影响)
1> select * from comment
2> go
id newsid val
----------- ----------- -----------

(0 行受影响)
1> select * from news
2> go
id caid val
----------- ----------- -----------

(0 行受影响)
1> select * from category
2> go
id val
----------- -----------

(0 行受影响)

Answer 3

具体报的是什么错误提示?