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
问题:创建外键约束,触发器就没用,怎么改触发器表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
解决方案 »
- 为何我把排序后的结果插入到表A中,但是select * from A得到的却是乱序
- 禁用触发器
- 如何根据出生日期,进行年龄段分组?
- 怎么同时插入多条记录和同时修改多条记录?
- 如何判斷某字符在字符串中的個數?
- 在连接到 SQL Server 2005 时,在默认的设置下 SQL Server 不允许进行远程连接可能会导致此失败。 (provider: SQL 网络接口
- 求SQL文
- SQL服务自动停止,可能是配置问题,大哥们帮我看看!
- 保证这两个操作同步,建立主外键关系,还是用事务来做好,或者用触发器好?
- 一个有关使用索引的问题
- VS SQL 数据挖掘
- sqlserver2008的 查询分析器如何执行外部os命令
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]
触发器这样写
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
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
2.dearbinge的方法第一次不行,要第二次才可以,但禁用约束,一直都禁用了
3.Carryontilltomorrow的方法有点看不懂谢谢大家