如何在闭包表中正确插入自引用行及处理关联逻辑？

你不需要每次手动检查并创建后代的自引用行，有两种更规范的方式解决这个问题：

方案一：调整插入SQL逻辑，自动补全父节点路径 #

如果不想依赖触发器，可以直接修改插入子节点的SQL，确保即使父节点没有在TreePaths中存在任何记录（包括自引用），也能生成所有必要的路径：

INSERT INTO TreePaths (ancestor, descendant, level)
-- 生成父节点及其所有祖先到新节点的路径
SELECT 
    COALESCE(t.ancestor, c.comment_id),
    2,
    COALESCE(t.level, 0) + 1
FROM Comments c
LEFT JOIN TreePaths t ON t.descendant = c.comment_id
WHERE c.comment_id = 1 -- 指定父节点ID
UNION ALL
-- 插入新节点的自引用
SELECT 2, 2, 1; -- 指定新节点ID

这个逻辑通过LEFT JOIN关联Comments表，确保即使父节点没有在TreePaths中存在记录，也能获取到父节点ID，生成父节点到新节点的路径；同时保留原有的UNION ALL部分插入新节点的自引用。

方案二：用触发器自动维护自引用行 #

更彻底的方法是给Comments表创建插入触发器，每当新增评论时，自动在TreePaths中插入对应的自引用行：

CREATE TRIGGER trg_Comments_Insert_TreePaths
ON dbo.Comments
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON; -- 避免返回影响行数的额外信息
    INSERT INTO dbo.TreePaths (ancestor, descendant, level)
    SELECT comment_id, comment_id, 1
    FROM inserted;
END;

这样所有新评论在插入Comments表后，都会自动拥有TreePaths中的自引用行，后续插入子节点时，直接使用你原来的SQL语句即可，无需再手动处理父节点的自引用问题。

总结 #

• 方案一适合临时场景或不想修改数据库结构的情况，通过调整SQL逻辑规避手动检查的麻烦；
• 方案二更适合长期维护，通过触发器标准化自引用行的创建，避免人为遗漏。

内容的提问来源于stack exchange，提问作者RheinmetallSkorpion