sql删除多表关联数据？？？

[lxg1508]

小弟现在有4个表，A、B、C、D，其中A、B间有主外健关系，B、C间有外健关系，C、D间有外健关系，现在要删除A表中的一条记录，同时要删除B、C、D中与A表相关联的数据，我该怎么做呢，最好给个例子，多谢！

[SQL77]

写触发器,一级一级的删除

[SQL77]

CREATE TRIGGER TRI_DE ON A FOR DELETE
AS
  BEGIN
         DELETE D FROM DELETED E,B,C,D 
         WHERE E.COL=B.COL AND B.COL1=C.COL1 AND C.COL2=D.COL2

         DELETE C FROM DELETED D,B,C WHERE D.COL=B.COL AND B.COL1=C.COL1

         DELETE B FROM DELETED D,B WHERE D.COL=B.COL 
  END

?

[Mr_Nice]

方法1、如楼上
方法2、本身如果有外键关系的话，用级联删除也是可以的。参考联机丛书的级联引用完整性约束

[--小F--]

级联删除?

[Mr_Nice]

[Quote=引用 4 楼 fredrickhu 的回复:]
级联删除?
[/Quote]

ON DELETE CASCADE

[sfyhip]

正在学习中，顶一下

[lxg1508]

[Quote=引用 2 楼 sql77 的回复:]
SQL codeCREATETRIGGER TRI_DEON AFORDELETEASBEGINDELETE DFROM DELETED E,B,C,DWHERE E.COL=B.COLAND B.COL1=C.COL1AND C.COL2=D.COL2DELETE CFROM DELETED D,B,CWHERE D.COL=B.COLAND B.COL1=C.COL1DELETE BFROM ?-
[/Quote]

好像不行哦

[yuhui]

定义好外键约束，用级联删除就可以啊！

[wang913com]

删除顺序 为 D C B A就行了

[lxg1508]

不会用级联啊

[lxg1508]

我也知道要 D C B A删除，但能不能给点具体的代码？

[lxg1508]

A表：
AID Aname 主健：AID

B表：
BID BelongAID Bname 主健：BID，外健：BelongAID

C表：
CID BelongBID Cname 主健：CID，外健：BelongBID

D表：
DID BelongCID Dname 主健：DID，外健：BelongCID

其中：
A表和B表通过A.AID和B.BelongAID 创建了外健关系
B表和C表通过B.BID和C.BelongBID 创建了外健关系
C表和D表通过C.CID和D.BelongCID 创建了外健关系

非常希望能给例子，同时非常感谢！

[xjmcky]

最好是用存储过程吧，在存储过程里面建立事务，这样做是最安全的，要删一起删！

[lxg1508]

我做的触发器：

ALTER TRIGGER Tri_Del
ON dbo.bigCategroyInfo
INSTEAD OF DELETE
AS
DECLARE @BID INT,@MID INT,@DID INT,@Mcount INT,@Dcount INT,@Ncount INT
SET @BID=(SELECT ID FROM DELETED)
SET @Mcount=(SELECT COUNT(0) FROM smallCategroy WHERE belongBigCategroyID=(@BID))
IF @Mcount > 0
BEGIN
SET @MID=(SELECT ID FROM smallCategroy WHERE belongBigCategroyID=@BID)
SET @Dcount=(SELECT COUNT(0) FROM deviceDetaileInfo WHERE belongSmallCategoryID IN (@MID))
IF @Dcount > 0
BEGIN
SET @DID=(SELECT ID FROM deviceDetaileInfo WHERE belongSmallCategoryID IN (@MID))
SET @Ncount=(SELECT COUNT(0) FROM deviceNumberInfo WHERE belongDetaileDeviceID IN (@DID))
IF @Ncount > 0
BEGIN
DELETE FROM deviceNumberInfo WHERE belongDetaileDeviceID IN (@DID)
DELETE FROM deviceDetaileInfo WHERE belongSmallCategoryID IN (@MID)
DELETE FROM smallCategroy WHERE belongBigCategroyID=@BID
DELETE FROM bigCategroyInfo WHERE ID=@BID
END
ELSE
BEGIN
DELETE FROM deviceDetaileInfo WHERE belongSmallCategoryID IN (@MID)
DELETE FROM smallCategroy WHERE belongBigCategroyID=@BID
DELETE FROM bigCategroyInfo WHERE ID=@BID
END
END
ELSE
BEGIN
DELETE FROM smallCategroy WHERE belongBigCategroyID=@BID
DELETE FROM bigCategroyInfo WHERE ID=@BID
END
END
ELSE
BEGIN
DELETE FROM bigCategroyInfo WHERE ID=@BID
END

提示：

子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后，或子查询用作表达式时，这种情况是不允许的。

[lxg1508]

也做过存储过程：
出现同样的问题。

[xjmcky]

触发器对服务器的消耗很大，没有存储过程效率高！大多应用采用存储过程，而放弃或少用触发器！

REATE PROCEDURE DELETE_JLSC (@AID integer) AS
begin
declare @err1 integer
declare @err2 integer
declare @err3 integer
declare @err4 integer
begin tran
delete D where BelongCID in (select CID from C where BelongBID in (select BID from B where BelongAID=@AID))
set @err1 = @@rowcount
delete C where BelongBID in (select BID from B where BelongAID=@AID)
set @err2 = @@rowcount
delete B where BelongAID = @AID
set @err3 = @@rowcount
delete A where AID=@AID
set @err4 = @@rowcount

if @err1 > -1 and @err2 >-1 and @err3 >-1 and @err4 >-1
commit tran
else
rollback tran

end
GO

[yyyyyhhhhwwww]

casecade

[Sybase数据库恢复]

创建外键或者reference的时候，加上ON DELETE CASCADE吧！

[lxg1508]

[Quote=引用 16 楼 xjmaccp 的回复:]
触发器对服务器的消耗很大，没有存储过程效率高！大多应用采用存储过程，而放弃或少用触发器！

REATE PROCEDURE DELETE_JLSC (@AID integer) AS
begin
declare @err1 integer
declare @err2 integer
        declare @err3 integer
declare @err4 integer
      begin tran
delete D where BelongCID in (select CID from C where BelongBID in (select BID from B where BelongAID=@AID))
set @err1 =  @@rowcount 
delete C where BelongBID in (select BID from B where BelongAID=@AID)
set @err2 =  @@rowcount 
        delete B where BelongAID = @AID
set @err3 =  @@rowcount 
delete A where AID=@AID
        set @err4 =  @@rowcount
 
if  @err1 > -1  and  @err2  >-1 and  @err3  >-1 and  @err4  >-1
  commit tran
else
  rollback tran
   
end
GO
[/Quote]

楼上的好像可以，我先去试试，谢谢~

[lxg1508]

[Quote=引用 16 楼 xjmaccp 的回复:]
触发器对服务器的消耗很大，没有存储过程效率高！大多应用采用存储过程，而放弃或少用触发器！

REATE PROCEDURE DELETE_JLSC (@AID integer) AS
begin
declare @err1 integer
declare @err2 integer
        declare @err3 integer
declare @err4 integer
      begin tran
delete D where BelongCID in (select CID from C where BelongBID in (select BID from B where BelongAID=@AID))
set @err1 =  @@rowcount 
delete C where BelongBID in (select BID from B where BelongAID=@AID)
set @err2 =  @@rowcount 
        delete B where BelongAID = @AID
set @err3 =  @@rowcount 
delete A where AID=@AID
        set @err4 =  @@rowcount
 
if  @err1 > -1  and  @err2  >-1 and  @err3  >-1 and  @err4  >-1
  commit tran
else
  rollback tran
   
end
GO
[/Quote]


非常感谢，问题解决了~~~