自引用表如何级联删除？？？

Question

有类似这样一个表：
分类表：CREATE TABLE [Class] ( 
[Id] int identity(1,1)  NOT NULL,
[Name] nvarchar(30) NOT NULL,
[Description] nvarchar(500),
[ParentClassId] int
)
ALTER TABLE [Class] ADD CONSTRAINT [PK_Class] 
PRIMARY KEY CLUSTERED ([Id])
ALTER TABLE [Class] ADD CONSTRAINT [FK_Class_Class] 
FOREIGN KEY ([ParentClassId]) REFERENCES [Class] ([Id])
字段ParentClassId为这个分类的上级分类，结构类似文件目录。
我想实现级联删除的功能：删除一个分类，级联删除所有的子分类（以及子分类的子分类）
试了下不能设置此级联删除，所以想用触发器实现。
可是这个触发器该怎么写呢？

Answer 1

参考如下的内容,然后自己稍做修改即可./*
标题：SQL SERVER 2000中查询指定节点及其所有子节点的函数(表格形式显示)
作者：爱新觉罗·毓华(十八年风雨,守得冰山雪莲花开) 
时间：2008-05-12
地点：广东深圳
*/create table tb(id varchar(3) , pid varchar(3) , name varchar(10))
insert into tb values('001' , null  , '广东省')
insert into tb values('002' , '001' , '广州市')
insert into tb values('003' , '001' , '深圳市')
insert into tb values('004' , '002' , '天河区')
insert into tb values('005' , '003' , '罗湖区')
insert into tb values('006' , '003' , '福田区')
insert into tb values('007' , '003' , '宝安区')
insert into tb values('008' , '007' , '西乡镇')
insert into tb values('009' , '007' , '龙华镇')
insert into tb values('010' , '007' , '松岗镇')
go--查询指定节点及其所有子节点的函数
create function f_cid(@ID varchar(3)) returns @t_level table(id varchar(3) , level int)
as
begin
  declare @level int
  set @level = 1
  insert into @t_level select @id , @level
  while @@ROWCOUNT > 0
  begin
    set @level = @level + 1
    insert into @t_level select a.id , @level
    from tb a , @t_Level b
    where a.pid = b.id and b.level = @level - 1
  end
  return
end
go--调用函数查询001(广东省)及其所有子节点
select a.* from tb a , f_cid('001') b where a.id = b.id order by a.id
/*
id   pid  name       
---- ---- ---------- 
001  NULL 广东省
002  001  广州市
003  001  深圳市
004  002  天河区
005  003  罗湖区
006  003  福田区
007  003  宝安区
008  007  西乡镇
009  007  龙华镇
010  007  松岗镇（所影响的行数为 10 行）
*/--调用函数查询002(广州市)及其所有子节点
select a.* from tb a , f_cid('002') b where a.id = b.id order by a.id
/*
id   pid  name       
---- ---- ---------- 
002  001  广州市
004  002  天河区（所影响的行数为 2 行）
*/--调用函数查询003(深圳市)及其所有子节点
select a.* from tb a , f_cid('003') b where a.id = b.id order by a.id
/*
id   pid  name       
---- ---- ---------- 
003  001  深圳市
005  003  罗湖区
006  003  福田区
007  003  宝安区
008  007  西乡镇
009  007  龙华镇
010  007  松岗镇（所影响的行数为 7 行）
*/drop table tb
drop function f_cid@@ROWCOUNT：返回受上一语句影响的行数。
返回类型：integer。
注释：任何不返回行的语句将这一变量设置为 0 ，如 IF 语句。
示例：下面的示例执行 UPDATE 语句并用 @@ROWCOUNT 来检测是否有发生更改的行。UPDATE authors SET au_lname = 'Jones' WHERE au_id = '999-888-7777'
IF @@ROWCOUNT = 0
   print 'Warning: No rows were updated'结果：(所影响的行数为 0 行)
Warning: No rows were updated/*
标题：SQL SERVER 2005中查询指定节点及其所有子节点的方法(表格形式显示)
作者：爱新觉罗·毓华(十八年风雨,守得冰山雪莲花开) 
时间：2010-02-02
地点：新疆乌鲁木齐
*/create table tb(id varchar(3) , pid varchar(3) , name nvarchar(10))
insert into tb values('001' , null  , N'广东省')
insert into tb values('002' , '001' , N'广州市')
insert into tb values('003' , '001' , N'深圳市')
insert into tb values('004' , '002' , N'天河区')
insert into tb values('005' , '003' , N'罗湖区')
insert into tb values('006' , '003' , N'福田区')
insert into tb values('007' , '003' , N'宝安区')
insert into tb values('008' , '007' , N'西乡镇')
insert into tb values('009' , '007' , N'龙华镇')
insert into tb values('010' , '007' , N'松岗镇')
goDECLARE @ID VARCHAR(3)--查询ID = '001'的所有子节点
SET @ID = '001'
;WITH T AS
(
  SELECT ID , PID , NAME 
  FROM TB
  WHERE ID = @ID
  UNION ALL
  SELECT A.ID , A.PID , A.NAME 
  FROM TB AS A JOIN T AS B ON A.PID = B.ID
)
SELECT * FROM T ORDER BY ID
/*
ID   PID  NAME
---- ---- ----------
001  NULL 广东省
002  001  广州市
003  001  深圳市
004  002  天河区
005  003  罗湖区
006  003  福田区
007  003  宝安区
008  007  西乡镇
009  007  龙华镇
010  007  松岗镇(10 行受影响)
*/--查询ID = '002'的所有子节点
SET @ID = '002'
;WITH T AS
(
  SELECT ID , PID , NAME 
  FROM TB
  WHERE ID = @ID
  UNION ALL
  SELECT A.ID , A.PID , A.NAME 
  FROM TB AS A JOIN T AS B ON A.PID = B.ID
)
SELECT * FROM T ORDER BY ID
/*
ID   PID  NAME
---- ---- ----------
002  001  广州市
004  002  天河区(2 行受影响)
*/--查询ID = '003'的所有子节点
SET @ID = '003'
;WITH T AS
(
  SELECT ID , PID , NAME 
  FROM TB
  WHERE ID = @ID
  UNION ALL
  SELECT A.ID , A.PID , A.NAME 
  FROM TB AS A JOIN T AS B ON A.PID = B.ID
)
SELECT * FROM T ORDER BY ID
/*
ID   PID  NAME
---- ---- ----------
003  001  深圳市
005  003  罗湖区
006  003  福田区
007  003  宝安区
008  007  西乡镇
009  007  龙华镇
010  007  松岗镇(7 行受影响)
*/drop table tb--注：除ID值不一样外，三个SQL语句是一样的。

Answer 2

不能用联级删除,联级删除不支持 自引用.GO
CREATE TRIGGER tri_test ON Class
INSTEAD OF DELETE
AS
    ;WITH Liang AS
     (
         SELECT A.* FROM Class AS A
             JOIN deleted AS B
         ON A.Id = B.Id
         UNION ALL
         SELECT B.*
         FROM Liang AS A
             JOIN Class AS B
         ON A.Id = B.ParentClassId
     )
     DELETE A FROM Class AS A
          JOIN Liang AS B
      ON A.Id = B.Id
GO

Answer 3

同步删除被删除节点的所有子节点(BOM节点删除) 收藏 
CREATE TABLE tb(ID int,PID int,Name nvarchar(10))
INSERT tb SELECT 1,NULL,'山东省'
UNION ALL SELECT 2,1   ,'烟台市'
UNION ALL SELECT 4,2   ,'招远市'
UNION ALL SELECT 3,1   ,'青岛市'
UNION ALL SELECT 5,NULL,'四会市'
UNION ALL SELECT 6,5   ,'清远市'
UNION ALL SELECT 7,6   ,'小分市'
GO--删除处理触发器(同步删除被删除节点的所有子节点)
CREATE TRIGGER tr_DeleteNode ON tb
FOR DELETE
AS
IF @@ROWCOUNT=0 RETURN --如果没有满足删除条件的记录,直接退出
--查找所有被删除节点的子节点
DECLARE @t TABLE(ID int,Level int)
DECLARE @Level int
SET @Level=1
INSERT @t SELECT a.ID,@Level
FROM tb a,deleted d
WHERE a.PID=d.ID
WHILE @@ROWCOUNT>0
BEGIN
    SET @Level=@Level+1
    INSERT @t SELECT a.ID,@Level
    FROM tb a,@t b
    WHERE a.PID=b.ID
        AND b.Level=@Level-1
END
DELETE a
FROM tb a,@t b
WHERE a.ID=b.ID
GO--删除
DELETE FROM tb WHERE ID in(2,3,5)
SELECT * FROM tb
/*--结果
ID          PID        Name 
---------------- ----------------- ---------- 
1           NULL      山东省
--*/if object_id('tb') is not null
   drop table tb 
gocreate table tb(id int,name varchar(50),parentId int)
goinsert into tb 
select 1 ,   '工具',   0  union all
select 2 ,   '工具1',  1 union all
select 3 ,   '工具2',  1 union all
select 4 ,   '工具3',  1 union all
select 6 ,   '工具21',  2 union all
select 7 ,   '工具311', 3 union all
select 8 ,   '工具211', 6 union all
select 9 ,   '系统',    0 union all
select 10,   '系统1' ,  9 go -- 2000 写法if object_id('dbo.f_getchirdNode()') is not null
   drop function dbo.f_getchirdNode
gocreate function f_getchirdNode(@id int)
returns @t_level table(id varchar(3) , level int)
begin
  declare @level int
  set @level = 1
  insert into @t_level select @id , @level
  while @@ROWCOUNT > 0
  begin
    set @level = @level + 1
    insert into @t_level select a.id , @level
    from tb a , @t_Level b
    where a.parentId = b.id and b.level = @level - 1
  end
  return
end
go
select * from tb a where exists (select * from f_getchirdNode(1) b where a.id = b.id) -- 查询
delete from tb where exists (select * from f_getchirdNode(1) b where tb.id = b.id) -- 删除
go-- 2005
declare @id int
set @id = 1
;with t as
(
   select * from tb where id = @id 
   union all
   select a.* from tb a,t b where a.parentId = b.id
)
delete from tb where exists (select 1 from t where tb.id =  id) -- 删除
--select * from t --查询
select * from tbdrop table tb
本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/htl258/archive/2009/04/28/4132602.aspx

Answer 4

create table tb(id varchar(3) , pid varchar(3) , name varchar(10))
insert into tb values('001' , null  , '广东省')
insert into tb values('002' , '001' , '广州市')
insert into tb values('003' , '001' , '深圳市')
insert into tb values('004' , '002' , '天河区')
insert into tb values('005' , '003' , '罗湖区')
insert into tb values('006' , '003' , '福田区')
insert into tb values('007' , '003' , '宝安区')
insert into tb values('008' , '007' , '西乡镇')
insert into tb values('009' , '007' , '龙华镇')
insert into tb values('010' , '007' , '松岗镇')
go--查询指定节点及其所有子节点的函数
create function f_cid(@ID varchar(3)) returns @t_level table(id varchar(3) , level int)
as
begin
  declare @level int
  set @level = 1
  insert into @t_level select @id , @level
  while @@ROWCOUNT > 0
  begin
    set @level = @level + 1
    insert into @t_level select a.id , @level
    from tb a , @t_Level b
    where a.pid = b.id and b.level = @level - 1
  end
  return
end
go--原数据
select * from tb
/*
id   pid  name       
---- ---- ---------- 
001  NULL 广东省
002  001  广州市
003  001  深圳市
004  002  天河区
005  003  罗湖区
006  003  福田区
007  003  宝安区
008  007  西乡镇
009  007  龙华镇
010  007  松岗镇（所影响的行数为 10 行）
*/--调用函数删除003(深圳市)及其所有子节点
delete tb from tb t where id in 
(select a.id from tb a , f_cid('003') b where a.id = b.id)--删除后的数据
select * from tb
/*
id   pid  name       
---- ---- ---------- 
001  NULL 广东省
002  001  广州市
004  002  天河区（所影响的行数为 3 行）
*/drop table tb
drop function f_cid

Answer 5

--针对你的表结构,大致为:
--查询指定节点及其所有子节点的函数
create function f_cid(@ID int) returns @t_level table(id int , level int)
as
begin
  declare @level int
  set @level = 1
  insert into @t_level select @id , @level
  while @@ROWCOUNT > 0
  begin
    set @level = @level + 1
    insert into @t_level select a.id , @level
    from tb a , @t_Level b
    where a.ParentClassId = b.id and b.level = @level - 1
  end
  return
end
godelete tb from tb t where id in 
(select a.id from tb a , f_cid(你输入的某个ID) b where a.id = b.id)

Answer 6

用这个吧
先找出所有子节点 然后都删除
前提楼主你是SQL2005

Answer 7

利用表上的 INSTEAD OF触发器 替代你的DELETE操作
触发器里面 首先利用CTE递归性质找出所有子节点 然后进行表的连接删除操作

Answer 8

2005的CTE递归 查询出所有子节点,然后删除 建议楼主去看看CTE的用法

Answer 9

看了下CTE递归，大概明白了。
不过2楼那个
SELECT A.* FROM Class AS A
             JOIN deleted AS B
         ON A.Id = B.Id
是做什么的？直接select from deleted就行了吧。我改成下面这个，用的很正常。CREATE TRIGGER TGR_DeleteSubClasses ON Class
INSTEAD OF DELETE
AS
    WITH ClassesToDelete AS
(
         SELECT Id FROM deleted
         UNION ALL
         SELECT Class.Id
         FROM Class
             JOIN ClassesToDelete
         ON Class.ParentClassId = ClassesToDelete.Id
)

DELETE Class FROM Class
JOIN ClassesToDelete
ON Class.Id = ClassesToDelete.Id