防止SQL树形结构中无限循环的触发器

Question

我有一个节点表格，包含(NodeId, NodeName)和结构表格(ParentNodeId, ChildNodeId)。我该如何编写一个触发器来检查插入、更新或删除语句是否会导致无限关系？

- Sorman

Answer 1

这是我的解决方案，目前它的表现符合预期。

CREATE TRIGGER [dbo].[CheckNodeDependence] ON [dbo].[ObjectTrees]
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @CTable TABLE(ChildId INT NOT NULL,
                          ParentId INT NOT NULL,
                          [Level] INT NOT NULL,
                          RowId INT NOT NULL)

    DECLARE @Level INT
    SET @Level = 1

    DECLARE @rows_affected INT
    SET @rows_affected = 1

    INSERT INTO @CTable
    SELECT ObjectId, ParentId, 1, ObjectId FROM INSERTED

    WHILE @rows_affected > 0
    BEGIN
        SET @Level = @Level + 1
        INSERT INTO @CTable
        SELECT T.ObjectId, T.ParentId, @Level, C.RowId
            FROM ObjectTrees T
                INNER JOIN @CTable C ON T.ParentId = C.ChildId
                AND C.Level = @Level - 1

            SET @rows_affected = @@rowcount
            IF EXISTS(
                SELECT * FROM @CTable B
                    INNER JOIN @CTable V ON B.level = 1
                    AND V.Level > 1
                    AND V.RowId = B.RowId
                    AND V.ChildId = B.RowId)
            BEGIN
                    DECLARE @error_message VARCHAR(200)
                    SET @error_message = 'Operation would cause illegal circular reference in tree structure, level = ' + CAST(@Level AS VARCHAR(30))
                    RAISERROR(@error_message,16,1)
                    ROLLBACK TRANSACTION
                    RETURN
            END
        END
    END
GO

- Sorman

Answer 2

你需要递归地检查循环依赖条件，其中父级不会成为它自己的子级，直接或间接地。

在 SQL Server 2005 中，你可以为此编写递归 CTE。一个例子 -

WITH [RecursiveCTE]([Id], [ParentAccountId]) AS
(
    SELECT
        [Id],
        [ParentAccountId]
    FROM [Structure]
    WHERE [Id] = @Id
    UNION ALL
    SELECT
        S.[Id],
        S.[ParentAccountId]
    FROM [Structure] S INNER JOIN [RecursiveCTE] RCTE ON S.[ParentAccountId] = RCTE.[Id]
)
SELECT * FROM [RecursiveCTE]

- Kirtan

---

但是，一旦找到了，使用触发器使插入/更新失败的最佳方法是什么？ - MatBailie

这取决于平台。在Oracle中，你可以抛出异常，希望文本中包含尽可能多的有用信息。但实际上，这取决于应用程序，不是吗？ - Steve Broberg

是的。这取决于您使用的应用程序。在SQL Server中，您也可以抛出异常来停止插入。或者您可以使用Instead Of触发器并什么都不做。 - Kirtan

我同意两位评论者的观点，我的意思是提问者想知道如何找到问题，然后使用触发器来防止它的发生 :) - MatBailie