数据库中有 Info 表,主键为 ID,其中一个字段 ParentID 记录父记录 ID,由此构成树型结构。当删除一条记录时,需要同时删除所有子孙记录,使用触发器来执行此项操作:
--
=============================================
-- 此触发器在删除信息时同时删除每个信息的下级信息
--
-- 此触发器的作用是保证在删除树型结构的信息时不会留下零碎的树枝。
-- 此触发器应被递归执行。如无法递归,请执行:
-- ALTER DATABASE [DBName]
-- SET RECURSIVE_TRIGGERS ON
-- 语句使之可行。
-- =============================================
ALTER TRIGGER [ dbo ] . [ DeleteSubInfos ]
ON [ dbo ] . [ Info ]
AFTER DELETE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON ;
if (( select count (id) from deleted) > 0 )
Delete From [ Info ] Where [ ParentID ] in ( Select [ ID ] From [ deleted ] );
END
-- 此触发器在删除信息时同时删除每个信息的下级信息
--
-- 此触发器的作用是保证在删除树型结构的信息时不会留下零碎的树枝。
-- 此触发器应被递归执行。如无法递归,请执行:
-- ALTER DATABASE [DBName]
-- SET RECURSIVE_TRIGGERS ON
-- 语句使之可行。
-- =============================================
ALTER TRIGGER [ dbo ] . [ DeleteSubInfos ]
ON [ dbo ] . [ Info ]
AFTER DELETE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON ;
if (( select count (id) from deleted) > 0 )
Delete From [ Info ] Where [ ParentID ] in ( Select [ ID ] From [ deleted ] );
END