Tree traversal data structure – stored procedures

In the first part of this article I wrote about tree traversal database structure. Now I want to show you how to prepare all stored procedures and triggers to be able to work with it effectively. From the first part we have already database structure and we filled the table with data. Now we need to have these data always with correct informations.

What can happen

There are many scenarios what can happen and you need to recreate lft and rtg in the tree. For inserting new category, deleting old one and move category under another parent, we can use one procedure:

ALTER PROCEDURE dbo.RecreateCategoryTree
  @id INT = NULL,
  @cnt INT = 0
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @actId INT,
    @actLft INT,
    @actRtg INT;
  DECLARE categories_cursor CURSOR LOCAL
  FOR
    SELECT ID, lft, rtg
    FROM dbo.Categories
    WHERE (ParentID = @id) OR (@id IS NULL AND ParentID IS NULL);
  OPEN categories_cursor
  FETCH NEXT FROM categories_cursor
  INTO @actId, @actLft, @actRtg

  WHILE @@FETCH_STATUS = 0
  BEGIN
    SET @cnt = @cnt + 1;

    UPDATE dbo.Categories SET lft = @cnt WHERE ID = @actID;

    EXEC @cnt = [dbo].[RecreateCategoryTree] @actId, @cnt;
    SET @cnt = @cnt + 1;

    UPDATE dbo.Categories SET rtg = @cnt WHERE ID = @actID;

    FETCH NEXT FROM categories_cursor
    INTO @actId, @actLft, @actRtg
  END
  CLOSE categories_cursor
  DEALLOCATE categories_cursor

  RETURN @cnt;
END
GO

When we call this stored procedure it will recreate all lft and rtg information in Categories table. Second step will be to create trigger what will be fired after insert, update or delete.

CREATE TRIGGER dbo.RecreateCategories
ON  dbo.Categories
AFTER INSERT,DELETE,UPDATE
AS
BEGIN
  SET NOCOUNT ON;
  EXEC [dbo].[RecreateCategoryTree]
END
GO

This is realy the easiest way. It will not be fast with many records. For table with a few hundred record and occasional update it’s enought. Now we can try if everything is working as we need. Try to insert into category:

INSERT INTO dbo.Categories (CategoryName, ParentID) VALUES ('Celeron', 2);