我现在的触发器是SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GOALTER trigger delete_tableName on t_Folder
for delete
asdelete from t_Folder where parentId in (select FolderId from deleted)GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO但是不知道为什么 比如我的父节点下有2个或者多个节点它还是只能删除一个节点 这个时候数据库会产生冗余数据
GO
SET ANSI_NULLS ON
GOALTER trigger delete_tableName on t_Folder
for delete
asdelete from t_Folder where parentId in (select FolderId from deleted)GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO但是不知道为什么 比如我的父节点下有2个或者多个节点它还是只能删除一个节点 这个时候数据库会产生冗余数据
;with f as
(
select * from tb where id=@id
union all
select a.* from tb a join f on a.id=b.pid
)delete from f
for delete
as
WITH a
AS
(
SELECT * FROM DELETED
UNION ALL
SELECT * FROM a INNER JOIN t_Folder AS b ON a.FolderId=b.parentId
)
DELETE t_Folder WHERE FolderId IN(SELECT FolderId FROM a)
USE [master]
GO
ALTER DATABASE Test SET RECURSIVE_TRIGGERS ON WITH NO_WAIT
--設置Test庫為觸發器遞歸,遞歸層數不超過32層
GO
USE Test
--指定數據庫
go
IF NOT OBJECT_ID('Tab') IS NULL
DROP TABLE Tab
Go
CREATE TABLE Tab
(
[Id] INT ,
[ParentId] INT ,
[Name] NVARCHAR(3)
)
INSERT Tab
SELECT 1 ,
1 ,
N'a1'
UNION ALL
SELECT 2 ,
2 ,
N'a2'
UNION ALL
SELECT 14 ,
1 ,
N'a13'
UNION ALL
SELECT 15 ,
1 ,
N'b13'
UNION ALL
SELECT 16 ,
14 ,
N'a14'
UNION ALL
SELECT 17 ,
14 ,
N'b14'
Go
CREATE TRIGGER tr_Tab ON Tab
AFTER DELETE
AS
BEGIN
IF @@rowcount > 0 AND @@NESTLEVEL<32
DELETE a
FROM Tab a
WHERE EXISTS ( SELECT 1
FROM deleted
WHERE ID = a.[ParentId] )
END
go
DELETE Tab
WHERE ID = 1SELECT *
FROM TabSELECT @@NESTLEVEL
for delete
as
DECLARE @t TABLE (FolderId INT,parentId int)
INSERT @t
SELECT b.FolderId,b.parentId
FROM DELETED AS a
INNER JOIN
t_Folder AS b ON a.FolderId=b.parentIdWHILE @@ROWCOUNT>0
INSERT @t
SELECT b.FolderId,b.parentId
FROM @t AS a
INNER JOIN
t_Folder AS b ON a.FolderId=b.parentId
WHERE NOT EXISTS(SELECT 1 FROM @t WHERE FolderId =b.FolderId)DELETE t_Folder WHERE FolderId IN(SELECT FolderId FROM @t)
测试脚本CREATE TABLE tb
(
FolderId INT ,
parentId INT ,
name NVARCHAR(3)
)
INSERT tb
SELECT 1 ,
1 ,
N'a1'
UNION ALL
SELECT 2 ,
2 ,
N'a2'
UNION ALL
SELECT 14 ,
1 ,
N'a13'
UNION ALL
SELECT 15 ,
1 ,
N'b13'
UNION ALL
SELECT 16 ,
14 ,
N'a14'
UNION ALL
SELECT 17 ,
14 ,
N'b14'
create trigger delete_tableName on tb
for delete
asdelete from tb where parentId in (select FolderId from deleted)GO--测试
delete from tb where folderid=1
还有值是否相同,我怀疑是FolderId 和 parentId 值不相同,parentId包含其他字符
ALTER DATABASE DBName SET RECURSIVE_TRIGGERS ON--
delete from tb where parentId in (select FolderId from deleted)方法错在哪里????