獲取樹形資料的全路徑

我們經常要用到樹形資料，比如：部門、文章欄目、行政區劃……，而且有時還需要用到這些資料的全路徑，比如：四川省 > 遂寧市 > 船山區。

為了避免每次要使用全路徑時都拼接一次，我們可以在資料庫增加一個欄位來儲存全路徑，並且使用觸發器來生成全路徑。

典型的表結構如下：

由於新增的節點都處於末級，沒有子節點，只需要修改新增節點的全路徑，如下：

ALTER TRIGGER [dbo].[trDeptInsert]
    ON [dbo].[tbDept]
    AFTER INSERT
AS
SET NOCOUNT ON
--連線inserted表得到新增的資料
--連線tbDept表取得上級節點路徑，如果是頂層，就沒有上級節點，所以需要LEFT JOIN
--如果是頂層，全路徑就是節點名稱，否則，就是上級節點全路徑+當前節點名稱
UPDATE A
SET Path = CASE WHEN A.ParentID = 0 THEN A.Title ELSE B.Path + ' > ' + A.Title END
FROM tbDept A INNER JOIN inserted I ON A.ID = I.ID LEFT JOIN tbDept B ON A.ParentID = B.ID

修改節點後，要做判斷，只有修改了節點名稱或者所屬上級節點，才需要修改全路徑，同時，也要修改所有子節點的全路徑，如下：

ALTER TRIGGER [dbo].[trDeptUpdate]
    ON [dbo].[tbDept]
    AFTER UPDATE
AS
SET NOCOUNT ON;
--遞迴查出修改了的節點以及他們的所有後代節點
--連線inserted和deleted表，並使用I.ParentID <> D.ParentID OR I.Title <> D.Title判斷是否修改了名稱和所屬父節點
WITH temp AS (
    SELECT A.ID FROM tbDept A INNER JOIN inserted I ON A.ID = I.ID INNER JOIN deleted D ON I.ID = D.ID
    WHERE I.ParentID <> D.ParentID OR I.Title <> D.Title
    UNION ALL
    SELECT tbDept.ID FROM temp INNER JOIN tbDept ON temp.ID = tbDept.ParentID
)
--CTE資料只能使用一次，所以需要寫入臨時表，同時增加行號，以便遍歷
SELECT ID, RowID = ROW_NUMBER() OVER(ORDER BY ID) INTO #TEMP FROM temp
--遍歷上面得到的所有節點ID
DECLARE @RowCount int = (SELECT COUNT(*) FROM #TEMP), @RowID int = 1    --行數，行號
WHILE @RowCount >= @RowID
BEGIN
    DECLARE @ID int
    SELECT @ID = ID FROM #TEMP WHERE RowID = @RowID;
    --遞迴得到當前節點及其所有父節點名稱
    WITH temp AS (
        SELECT Title, ParentID, Level = 0 FROM tbDept WHERE ID = @ID
        UNION ALL
        SELECT A.Title, A.ParentID, Level - 1 FROM tbDept A INNER JOIN temp ON A.ID = temp.ParentID
    )
    --使用FOR XML PATH拼接出節點全路徑
    --使用STUFF函數去掉前面多餘的連線符號
    --注意使用TYPE和.value('.','NVARCHAR(MAX)')，否則尖括號會被跳脫，並且value只能小寫
    UPDATE tbDept
    SET Path = STUFF((SELECT ' > '+Title FROM temp ORDER BY Level FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'),1,3,'')
    WHERE ID = @ID
    SET @RowID = @RowID + 1
END

資料範例如下：