i'm trying to create deleted trigger for my sql server database. After a record was deleted, the trigger execute and update left, right,... of other nodes in tree. Detail about Nested Set Model. Here's my sql code for deleted trigger:
ALTER TRIGGER [dbo].[delete-test-nested]
ON [dbo].[Test]
AFTER DELETE
AS
BEGIN
DECLARE @start int = 0, @end int
SELECT @end = count(*) from deleted
WHILE(@start < @end)
BEGIN
DECLARE @left int, @right int, @level int, @root int, @parentId int
SELECT @id = t.Id, @lft = t.Lft, @rgt = t.Rgt, @lvl = t.Lvl, @root = t.Root
FROM deleted t
ORDER BY t.Id OFFSET @offset_record ROWS FETCH NEXT 1 ROWS ONLY;
DELETE FROM Test WHERE Left > @Left AND Right < @Right AND Root = @Root
UPDATE Test SET Left = Left - (@Right - @Left + 1) WHERE Left > @Left AND Root = @Root
UPDATE Test SET Left = Left - (@Right - @Left + 1) WHERE Right > @Right AND Root = @Root
SET @start = @start+ 1;
END
END
Initial state:
+----+----------+------+-------+-------+------+------+--+
| Id | ParentId | Left | Right | Level | Text | Root | |
+----+----------+------+-------+-------+------+------+--+
| 1 | null | 1 | 18 | 1 | Test | 1 | |
| 2 | 1 | 2 | 3 | 2 | Test | 1 | |
| 3 | 1 | 4 | 11 | 2 | Test | 1 | |
| 4 | 3 | 5 | 6 | 3 | Test | 1 | |
| 5 | 3 | 7 | 10 | 3 | Test | 1 | |
| 6 | 5 | 8 | 9 | 4 | Test | 1 | |
| 7 | 1 | 12 | 17 | 2 | Test | 1 | |
| 8 | 7 | 13 | 14 | 3 | Test | 1 | |
| 9 | 7 | 15 | 16 | 3 | Test | 1 | |
+----+----------+------+-------+-------+------+------+--+
The code works fine for delete single record such as:
DELETE FROM Test
WHERE Id IN (2)
DELETE FROM Test
WHERE Id IN (3)
Correct:
+----+----------+------+-------+-------+------+------+--+
| Id | ParentId | Left | Right | Level | Text | Root | |
+----+----------+------+-------+-------+------+------+--+
| 1 | | 1 | 8 | 1 | Test | 1 | |
| 7 | 1 | 2 | 7 | 2 | Test | 1 | |
| 8 | 7 | 3 | 4 | 3 | Test | 1 | |
| 9 | 7 | 5 | 6 | 3 | Test | 1 | |
+----+----------+------+-------+-------+------+------+--+
But when i tried to delete mutiple records at sametime, the issue happen:
DELETE FROM Test
WHERE Id IN (2,3)
Wrong:
+----+----------+------+-------+-------+------+------+--+
| Id | ParentId | Left | Right | Level | Text | Root | |
+----+----------+------+-------+-------+------+------+--+
| 1 | null | 1 | 8 | 1 | Test | 1 | |
| 4 | 3 | 3 | 4 | 3 | Test | 1 | |
| 7 | 1 | 10 | 7 | 2 | Test | 1 | |
| 8 | 7 | 11 | 4 | 3 | Test | 1 | |
| 9 | 7 | 5 | 6 | 3 | Test | 1 | |
+----+----------+------+-------+-------+------+------+--+