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()returnsTRUEregardless of whether anINSERTorUPDATEattempt 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(@@PROCID) > 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