0

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 |  |
+----+----------+------+-------+-------+------+------+--+
MaybeNextTime
  • 561
  • 5
  • 11
  • Using a `WHILE` in a `TRIGGER` seems like a bad idea to start off with., and likely why what you have isn't working. What is the goal of your trigger here exactly? – Thom A Oct 20 '19 at 10:57
  • The goal of trigger: after a record in table is delete, trigger exec and update left, right, level,... values of other nodes in tree – MaybeNextTime Oct 20 '19 at 10:59
  • That doesn't really explain anything. Add a description of your goal in your qusetion, and please replace the images of your data with tabular formatted `text` so that we have something to work with, please. – Thom A Oct 20 '19 at 11:13
  • Also, the **full** SQL for your Trigger will help. The SQL you've given us *looks* like you're using an `INSTEAD OF` trigger, rather than an `AFTER` trigger; impossible to know unless you tell us though. – Thom A Oct 20 '19 at 11:22
  • I just updated question – MaybeNextTime Oct 20 '19 at 12:00
  • You've replaced the images, which is great, but you haven't explained your goal. Thanks. – Thom A Oct 20 '19 at 12:01
  • As I mentioned, the goal of trigger is to update the left, right of other nodes in correct way (you can read about nested set model) when people deleting multiple records. I have correct result when delete records sequentially but in the second case, the updated values were wrong. – MaybeNextTime Oct 20 '19 at 13:45
  • You should not expect the volunteers here to read an (entire) Wikipedia article to understand your requirements. If you can't explain what you need, then you're very unlikely to get an answer. – Thom A Oct 20 '19 at 14:04

0 Answers0