SQL Server触发器实现Root插入时自连接Child表三级父子层级构建

核心思路 #

• 分三次插入三级Child数据，逐层级捕获生成的自增ID与对应RootID的映射关系，无需依赖Name字段关联，也无需使用SEQUENCE对象
• 使用表变量存储每一级生成的ChildID与RootID的对应关系，直接用于下一级的ParentID赋值，适配单条/批量插入Root的场景

完整触发器代码 #

CREATE OR ALTER TRIGGER [dbo].[Root_TR]
ON [dbo].[Root]
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON

    -- 存储LVL1的RootID与生成的ChildID映射
    DECLARE @LVL1_Map TABLE (
        RootID INT,
        LVL1_ChildID INT
    )
    -- 存储LVL2的RootID与生成的ChildID映射
    DECLARE @LVL2_Map TABLE (
        RootID INT,
        LVL2_ChildID INT
    )

    -- 第一步：插入LVL1数据，捕获生成的自增ID
    INSERT INTO [dbo].[Child] ([RootID], [Name], [ParentID])
    OUTPUT INSERTED.RootID, INSERTED.ChildID INTO @LVL1_Map(RootID, LVL1_ChildID)
    SELECT
        I.RootID,
        CONCAT_WS('_', I.Name, '1'),
        NULL
    FROM INSERTED I

    -- 第二步：插入LVL2数据，关联同Root下LVL1的ID作为父ID，同时捕获LVL2的自增ID
    INSERT INTO [dbo].[Child] ([RootID], [Name], [ParentID])
    OUTPUT INSERTED.RootID, INSERTED.ChildID INTO @LVL2_Map(RootID, LVL2_ChildID)
    SELECT
        I.RootID,
        CONCAT_WS('_', I.Name, '2'),
        M.LVL1_ChildID
    FROM INSERTED I
    INNER JOIN @LVL1_Map M ON I.RootID = M.RootID

    -- 第三步：插入LVL3数据，关联同Root下LVL2的ID作为父ID
    INSERT INTO [dbo].[Child] ([RootID], [Name], [ParentID])
    SELECT
        I.RootID,
        CONCAT_WS('_', I.Name, '3'),
        M.LVL2_ChildID
    FROM INSERTED I
    INNER JOIN @LVL2_Map M ON I.RootID = M.RootID
END
GO

测试验证 #

-- 单条插入测试
INSERT INTO [dbo].[Root] ([Name]) VALUES ('Foo')
-- 批量插入测试（可选）
INSERT INTO [dbo].[Root] ([Name]) VALUES ('Bar'), ('Baz')

-- 验证结果
SELECT * FROM [Root]
SELECT * FROM [Child] ORDER BY RootID, ChildID

执行后Child表中同RootID下的3条记录会自动形成LVL1->LVL2->LVL3的父子层级，ParentID关联完全符合预期。

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