SQL Server Recursive trigger hitting limit unexpectedly

I have a self-referencing table (parentID reference) which I want to set a hint ancestry column so that at any given level, a row can reveal it's parent, grand parent, etc. upwards.

CREATE TABLE Permissions (
  PermissionId  INT PRIMARY KEY,
  ParentPermisisonId INT REFERENCES Permissions (PermissionId),
  Ancestry VARCHAR(MAX)
);

So for instance if I have ID #2 referencing ID #1, and ID #3 referencing #2, the ancestry for ID #3 would be "~2~1~" This gives me a string that I can Split to get all relevant parent, grandparent, etc. values to build a single query without having to constantly query upwards using ParentPermissionId until it is #null.

The idea is to have a trigger on when a ParentPermissionId is changed to refresh not only this row's Ancestry, but any/all rows beneath it. I have enabled RECURSIVE_TRIGGERS on the database, and I have gotten the trigger working, though I had to include a check for the TRIGGER_NESTLEVEL to avoid it hitting the limit, though I'm concerned why it is hitting the limit.

This is the currently functional Trigger:

ALTER TRIGGER [Permissions_OnParentPermissionId_Change]
   ON  [Permissions] 
   AFTER INSERT,UPDATE
AS 
IF (UPDATE(ParentPermissionId))
BEGIN
    SET NOCOUNT ON;

    /* Update the ancestry of an item when a parent ID changes to reflect the 
       hierarchy of IDs leading to this entry. */
    UPDATE p
        SET p.Ancestry = CASE WHEN i.ParentPermissionId IS NULL THEN NULL ELSE ISNULL(parent.Ancestry, '~') + CAST(parent.PermissionId AS NVARCHAR(10)) + '~' END

    FROM [Permissions] p
    INNER JOIN Inserted i ON p.PermissionId = i.PermissionId
    LEFT JOIN [Permissions] parent ON i.ParentPermissionId = parent.PermissionId;

  IF TRIGGER_NESTLEVEL() > 6
     RETURN

    UPDATE child
    SET ParentPermissionId = parent.PermissionId
    FROM [Permissions] child
    INNER JOIN [Permissions] parent ON child.ParentPermissionId = parent.PermissionId
    INNER JOIN inserted i ON parent.PermissionId = i.PermissionId
  
END

The first statement goes and updates the ancestry of the updated item. It is the second UPDATE that gets hung up in the recursion if I don't have a NestLevel check to drop out. Yes, the 2nd statement is effectively just setting the ParentPermissionId to the value it already is, this is to leverage the trigger to recursively go down the children to update their hierarchy, then dive down further levels.

Now what is interesting is I wanted to see what the nesting calls looked like, so I created a simple debug table to capture a unique run ID (Guid) and the inserted ID being run through to see what was going on as the trigger ran. I added the following to the start of the Trigger:

DECLARE @id AS UNIQUEIDENTIFIER;
SET @id = NEWID();

INSERT INTO Debug
SELECT @id, i.PermissionId
FROM Inserted i

The data in question from the Permissions Table, in which the "edit" I made was changing PermissionId #1000's ParentPermissionId from #null to 0. This did update the ancestry of that row, and the children & down:

permissionid    parentpermissionid  ancestry
0               NULL                NULL
1000            0                   [0]
1001            1000                [0][1000]
1002            1000                [0][1000]
1003            1002                [0][1000][1002]
1004            1002                [0][1000][1002]
1005            1004                [0][1000][1002][1004]
1006            1000                [0][1000]
1007            1008                [0][1000][1006][1008]
1008            1006                [0][1000][1006]
1009            1008                [0][1000][1006][1008]
2000            NULL                NULL

The data captured: (Guid for each trigger call, and the IDs from "inserted"

Guid                                    DebugId
1DEF392F-006B-4DCF-869E-003F943AB36E    1000

340DCAD8-311D-42AF-8FA1-EAFBAB87AB07    1006
340DCAD8-311D-42AF-8FA1-EAFBAB87AB07    1002
340DCAD8-311D-42AF-8FA1-EAFBAB87AB07    1001

365DFF96-6718-4F04-B515-B09D7E10ED42    1008
365DFF96-6718-4F04-B515-B09D7E10ED42    1004
365DFF96-6718-4F04-B515-B09D7E10ED42    1003

FA4E089C-94FE-4C13-AF96-D332B9697042    1009
FA4E089C-94FE-4C13-AF96-D332B9697042    1007
FA4E089C-94FE-4C13-AF96-D332B9697042    1005

The first trigger had the single "inserted" value for #1000 as expected, The second trigger pass is also expected, having all of the direct children of #1000. (1001,1002, and 1006) The next trigger pass is children of 1001,1002,and 1006, then the children of 1003, 1004, and 1008.

This behaviour is pretty much what I expected to happen, but what I don't understand is why if I remove the TRIGGER_NESTLEVEL check, does this trigger recursion seem to trip the 32 depth limit for SQL Server? Originally to test I had tried a TRIGGER_NESTLEVEL of "1" and "3", where I observed with "1" it did only go 1-deep, while "3" worked, so I bumped it to "6" to see if it would reveal an issue with extra runs being captured or something, but there weren't any. (The above results are a NESTLEVEL of "6") If I set the NESTLEVEL check to "> 31" or even "> 30" I get an error exceeding 32. If I set it to "> 16" it works, but again with the same 10 rows returned.

While the solution does work with a TRIGGER_NESTLEVEL implemented, I am concerned that as I flesh out the hierarchy of data, a setting too low might not capture the depth I need (granted my goal is to keep this as flat as possible) or that the breadth or depth of the resulting relationships might still trigger an overlap.

Is there an explanation for why the 32 depth recursion guard is getting hit and a better approach to be able to track this?

In writing this out I did think to check total calls for the update trigger adding this above the Insert into the Debug table:

Insert into Debug select @id, -1

so:

IF (UPDATE(ParentPermissionId))
BEGIN
    SET NOCOUNT ON;

    DECLARE @id AS UNIQUEIDENTIFIER;
    SET @id = NEWID();

    Insert into Debug select @id, -1

    INSERT INTO Debug
    SELECT @id, i.PermissionId
    FROM Inserted i

    /* Update the ancestry of an item when a parent ID changes to reflect the 
       hierarchy of IDs leading to this entry. */

    ...

This does shed some light in that I can see the -1 row inserted before each "pass" for the trigger, but then at the end, with a NESTLEVEL of "6" there are 3 additional "-1" rows each with a new GUID. These calls don't result in an ID being written.

Guid                                    DebugId
...
B86AEC90-9C32-4107-9878-078E709658F9    -1
B86AEC90-9C32-4107-9878-078E709658F9    1009
B86AEC90-9C32-4107-9878-078E709658F9    1007
B86AEC90-9C32-4107-9878-078E709658F9    1005
03CAC966-BD98-4C68-9C94-8006948CA586    -1  *
1F84CCAE-9EEC-418B-944B-99350756220F    -1  *
C7ED82B4-DE03-4919-987A-72F77CD01BAB    -1  *

If I increase the NESTLEVEL to 16 I get a lot more empty -1 lines. So the trigger is firing extra times, IF(UPDATE(ParentPermissionId)) is coming up True, but then "inserted" is somehow empty since the SELECT from inserted doesn't insert anything?

I feel like I'm missing something crucial with the last update where all I want to do is ensure that as an item updates it's parent, it ensures that it's children also go through and update theirs. Adding:

IF (NOT EXISTS(SELECT 1 FROM INSERTED))
    RETURN

Prevents the extra -1 rows from inserting, but that doesn't solve the problem because the Trigger is still getting set off, which I believe is the crux of the problem.

Update: Actually, adding that above check to the trigger did appear to fix the recursion rule getting tripped. The resulting Trigger does not use the NESTLEVEL and doesn't trip the 32 level recursion rule:

ALTER TRIGGER [Permissions_OnParentPermissionId_Change]
   ON  [Permissions] 
   AFTER INSERT,UPDATE
AS 
IF (UPDATE(ParentPermissionId))
BEGIN
    SET NOCOUNT ON;

    IF (NOT EXISTS(SELECT 1 FROM INSERTED))
        RETURN 

    /* Update the ancestry of an item when a parent ID changes to reflect the 
       hierarchy of IDs leading to this entry. */
    UPDATE p
    SET p.Ancestry = CASE WHEN i.ParentPermissionId IS NULL THEN NULL ELSE ISNULL(parent.Ancestry, '~') + CAST(parent.PermissionId AS NVARCHAR(10)) + '~' END
    FROM [Permissions] p
    INNER JOIN Inserted i ON p.PermissionId = i.PermissionId
    LEFT JOIN [Permissions] parent ON i.ParentPermissionId = parent.PermissionId;

    UPDATE child
    SET ParentPermissionId = parent.PermissionId
    FROM [Permissions] child
    INNER JOIN [Permissions] parent ON child.ParentPermissionId = parent.PermissionId
    INNER JOIN inserted i ON parent.PermissionId = i.PermissionId
    WHERE child.ParentPermissionId = i.PermissionId AND child.ParentPermissionId IS NOT NULL

END

I'd still be very interested to learn why seemingly empty Updates were coming in that pass the ParentPermissionId Updated check but result in no Inserted rows.

The mistake is here:

IF (UPDATE(ParentPermissionId))

The documentation for UPDATE() clearly says

UPDATE() returns TRUE regardless of whether an INSERT or UPDATE attempt is successful.

In other words: there doesn't need to be any rows affected. And a trigger is always fired regardless of whether there are rows affected.

You need to add this condition (and you can reverse it and immediately RETURN to avoid nesting the whole trigger function).

IF NOT (UPDATE(ParentPermissionId) AND EXISTS (SELECT 1 FROM inserted))
    RETURN;

You should have this at the top of every trigger anyway, even no-recursive ones, to avoid running the code if there are no rows.

Having said that, there is a way of doing all this without using recursive triggers, such as a recursive CTE. For example, you could use the following trigger instead, which recurses using a recursive CTE in a single call rather than recursive calls. It will therefore allow recursion of more than 32 levels (but you should set MAXRECURSION in that case).

I can't say whether or not this is more efficient than what you have, but it may be.

CREATE OR ALTER TRIGGER [Permissions_OnParentPermissionId_Change]
   ON  [Permissions] 
   AFTER INSERT,UPDATE
AS 

SET NOCOUNT ON;

IF TRIGGER_NESTLEVEL(OBJECT_ID(N'dbo.Permissions_OnParentPermissionId_Change')) > 1   -- prevent any recursion
   OR NOT UPDATE(ParentPermissionId)   -- not updated
   OR NOT EXISTS (SELECT 1 FROM inserted)   -- no rows
    RETURN;   -- early bail-out

WITH cte AS (
    SELECT
      i.PermissionId,
      i.ParentPermissionId,
      Ancestry = CONCAT(ISNULL(parent.Ancestry, '~'), i.PermissionId, '~'),
      1 AS level
    FROM inserted i
    LEFT JOIN dbo.Permissions parent ON parent.PermissionId = i.ParentPermissionId

    UNION ALL

    SELECT
      child.PermissionId,
      child.ParentPermissionId,
      CONCAT(cte.Ancestry, child.PermissionId, '~'),
      cte.level + 1
    FROM cte
    JOIN dbo.Permissions child ON child.ParentPermissionId = cte.PermissionId
),
MaxLevel AS (
    SELECT *,
      rn = ROW_NUMBER() OVER (PARTITION BY cte.PermissionId ORDER BY cte.level DESC)
    FROM cte
)
UPDATE p
SET Ancestry = c.Ancestry
FROM dbo.Permissions p
JOIN MaxLevel c ON c.PermissionId = p.PermissionId
WHERE c.rn = 1;   -- take only the most recursed value for each ID

db<>fiddle

SQL Server Recursive trigger hitting limit unexpectedly

1条答案

相关问题

热门标签

最新问答