触发器和外键约束问题

[xiaoziguo1]

1.用触发器实现级联删除
问题：创建外键约束，触发器就没用，怎么改触发器

表1：
--创建帖子表
create table Invitation
(
ID int identity(1,1) not null primary key,
CategoryName varchar(50) not null,
InvitationTitle varchar(50) not null,
InvitationContent text not null
)

表2：
--创建评论表
create table Comment
(
ID int identity(1,1) not null primary key,
InvitationID int not null,
CommentContent text not null
)


--创建外键
alter table Comment
add constraint FK_Comment_Invitation
foreign key(InvitationID)references Invitation(ID)

--触发器
--1.删除帖子时候连同其下的评论一起删除
create trigger DeleteInvtation on Invitation for delete
as
declare @ID varchar(50)
select @ID=ID from Deleted
delete from Comment where InvitationID=@ID

[dearbinge]

create trigger DeleteInvtation on Invitation for delete
as
declare @ID varchar(50)
select @ID=ID from Deleted
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'--禁用约束
delete from Comment where InvitationID=@ID[code=SQL]

EXEC sp_msforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL' --启用约束
[/code]

[lighwind]

你这样好像删除不了的，因为外键约束
触发器这样写
create trigger DeleteInvation
on Invitation
instead of delete
as
declare @id int
set @id=(select id from deleted)
begin transaction
delete from Comment where InvitationID =@id
if(@@error!=0)
rollback transaction
else
begin
delete from Invitation where id=@id
if(@id ==0)
commit transaction
else
rollback transaction
end

[hlf1989]

--改用INSTEAD OF
create trigger DeleteInvtation on Invitation INSTEAD OF delete
as
declare @ID varchar(50)
select @ID=ID from Deleted
delete from Comment where InvitationID=@ID
delete from Invitation where ID=@ID

[xiaoziguo1]

1.hlf1989 的办法--改用INSTEAD OF可以了
2.dearbinge的方法第一次不行，要第二次才可以，但禁用约束，一直都禁用了
3.Carryontilltomorrow的方法有点看不懂

谢谢大家

[lighwind]

老大 就是添加了一个事务