这个是根据节点id(WebSiteNodeId)删除某个节点(及其下所有子节点)的存储过程,能够实现对表中数据进行删除。但因为添加节点时会在项目中创建目录,所以需要在删除节点(及其下子节点)后,将节点id号WebSiteNodeId output,在程序中根据传回的参数值进行删除文件夹。不知道该怎么做,请指教,最好把代码贴上来。谢谢!
ALTER PROCEDURE [dbo].[WebSiteNode_UpdateChilds]
@WebSiteNodeId int = -1
AS
If(@WebSiteNodeId < 1)
Begin
Raiserror('非法ID',16,1)
RollBack
End
Declare
--Update [WebSiteNode] Set DeleteFlag = 1 Where WebSiteNodeId = @WebSiteNodeId
DELETE FROM [TemplateData] WHERE WebSiteNodeId = @WebSiteNodeId
DELETE FROM [WebSiteNode] WHERE WebSiteNodeId = @WebSiteNodeId
Declare @count int
Select @count = Count(*)
From [WebSiteNode]
Where FatherId = @WebSiteNodeId
Print '子结点数' + Convert(varchar(20),@count)
If(@count > 0)
Begin
Declare @tmp Table(Id Int IDENTITY(1,1) Not Null,WebSiteNodeId int)
Insert Into @tmp Select WebSiteNodeId From [WebSiteNode] Where FatherId = @WebSiteNodeId
While(@count > 0)
Begin
Declare @currId int
Print @currId
Select @currId = WebSiteNodeId From @tmp Where Id = @count
Exec WebSiteNode_UpdateChilds @currId
Set @count = @count - 1
End
End
Else
Return -1
ALTER PROCEDURE [dbo].[WebSiteNode_UpdateChilds]
@WebSiteNodeId int = -1
AS
If(@WebSiteNodeId < 1)
Begin
Raiserror('非法ID',16,1)
RollBack
End
Declare
--Update [WebSiteNode] Set DeleteFlag = 1 Where WebSiteNodeId = @WebSiteNodeId
DELETE FROM [TemplateData] WHERE WebSiteNodeId = @WebSiteNodeId
DELETE FROM [WebSiteNode] WHERE WebSiteNodeId = @WebSiteNodeId
Declare @count int
Select @count = Count(*)
From [WebSiteNode]
Where FatherId = @WebSiteNodeId
Print '子结点数' + Convert(varchar(20),@count)
If(@count > 0)
Begin
Declare @tmp Table(Id Int IDENTITY(1,1) Not Null,WebSiteNodeId int)
Insert Into @tmp Select WebSiteNodeId From [WebSiteNode] Where FatherId = @WebSiteNodeId
While(@count > 0)
Begin
Declare @currId int
Print @currId
Select @currId = WebSiteNodeId From @tmp Where Id = @count
Exec WebSiteNode_UpdateChilds @currId
Set @count = @count - 1
End
End
Else
Return -1
@TableName sysname, --调整编码规则的表名
@FieldName sysname, --编码字段名
@CodeRule varchar(50), --以逗号分隔的编码规则,每层编码的长度,比如1,2,3,表示有三层编码,第一层长度为1,第二层长度为2,第三层长度为3
@Code varchar(50) --要删除的节点编码
AS
--参数检查
IF ISNULL(OBJECTPROPERTY(OBJECT_ID(@TableName),N'IsUserTable'),0)=0
BEGIN
RAISERROR(N'"%s"不存在,或者不是用户表',1,16,@TableName)
RETURN
END
IF NOT EXISTS(SELECT * FROM SYSCOLUMNS WHERE ID=OBJECT_ID(@TableName) AND name=@FieldName)
BEGIN
RAISERROR(N'列名"%s"在用户表"%s"中无效',1,16,@FieldName,@TableName)
RETURN
END
IF ISNULL(@CodeRule,'')=''
BEGIN
RAISERROR(N'必须编码规则字符串',1,16)
RETURN
END
IF PATINDEX(N'%[^0-9^,]%',@CodeRule)>0
BEGIN
RAISERROR(N'编码规则字符串"%s"中只能包含数字和逗号(,)',1,16,@CodeRule)
RETURN
END
IF ISNULL(@Code,'')='' RETURN--生成编码规则修改字符串
DECLARE @Pos1 int,@Pos2 int,@CodeLens int,
@New_CodeRule varchar(50),@s nvarchar(4000),
@Code1 varchar(100),@Code2 varchar(100)
SELECT @Pos1=1,
@Pos2=CHARINDEX(N',',@CodeRule+N',',@Pos1),
@CodeLens=SUBSTRING(@CodeRule,@Pos1,@Pos2-@Pos1)
WHILE @Pos2>=0 AND @CodeLens<LEN(@Code)
SELECT @Pos1=@Pos2+1,
@Pos2=CHARINDEX(N',',@CodeRule+N',',@Pos1),
@CodeLens=@CodeLens+SUBSTRING(@CodeRule,@Pos1,@Pos2-@Pos1)
IF @CodeLens<>LEN(@Code)
BEGIN
RAISERROR(N'编码"%s"不符合指定的编码规则"%s"',1,16,@Code,@CodeRule)
RETURN
END
SELECT @New_CodeRule=STUFF(@CodeRule,@Pos1,0,N'0,'),
@TableName=QUOTENAME(@TableName),
@FieldName=QUOTENAME(@FieldName),
@s=dbo.f_ChangeCodeRule(@CodeRule,@New_CodeRule,'',0,@FieldName),
@Code1=QUOTENAME(@Code+N'_%',N''''),
@Code2=QUOTENAME(@Code,N'''')--检查并完成删除处理
EXEC(N'BEGIN TRAN
--将处理后的编码与处理前的编码保存到临时表
SELECT Old_No='+@FieldName+N',New_No=('+@s+N')
INTO # FROM '+@TableName+N' WITH(XLOCK,TABLOCK)
WHERE '+@FieldName+N' LIKE '+@Code1+N'--检查更新后的编码是否存在重复
IF EXISTS(SELECT New_No FROM # GROUP BY New_No HAVING COUNT(*)>1)
SELECT * FROM # a
WHERE EXISTS(
SELECT * FROM # WHERE New_No=a.New_No AND Old_No<>a.Old_No)
ORDER BY New_No,Old_No
ELSE
--检查更新后的编码是否与表中现有的编码重复
IF EXISTS(SELECT * FROM '+@TableName+N' a,# b
WHERE a.'+@FieldName+N'<>'+@Code2+N'
AND a.'+@FieldName+N'=b.New_No
AND a.'+@FieldName+N'<>b.Old_No)
SELECT a.'+@FieldName+N',b.*
FROM '+@TableName+N' a,# b
WHERE a.'+@FieldName+N'<>'+@Code2+N'
AND a.'+@FieldName+N'=b.New_No
AND a.'+@FieldName+N'<>b.Old_No
ELSE
--如果编码处理后不重复,则更新到编码表中
DELETE FROM '+@TableName+N'
WHERE '+@FieldName+N'='+@Code2+N'
UPDATE a SET '+@FieldName+N'=b.New_No
FROM '+@TableName+N' a,# b
WHERE a.'+@FieldName+N'=b.Old_No
COMMIT TRAN')
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 山东省
--*/
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
调用这个函数然后 使用delete语句就可以把该节点的所有子孙删除了
/*
标题:查询指定节点及其所有子节点的函数
作者:爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间: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