Quote:
Originally Posted by Wim
I'm no smarty, so I haven't read his book.
Can you give an example dataset before and after, so I can figure out what you want?
|
This is the data:
Code:
groupId lft rgt
138 10 11
140 12 13
148 3 4
802 9 14
945 6 7
963 4 5
980 1 8
Quote:
Originally Posted by blindman
Basically, you will increment the right node of your parent node by 2 (along with all subsequent nodes of course), and then set the new child's left node to the parents old right node, and the child's right node to 1 higher than that.
|
Can you help me modify my existing procedure (by Joe Celko), to do that? I can't get it working. Another problem that I ran into when trying to solve it, is when the note given as parent node has no childs (and therefor not really a parent node), then it should become a parent node, with the new node as a the only child.
Here is Joe Celko's SP (which he credit to Mark E. Barney), that will insert the new node as a child to the rightmost child of the parent node:
Code:
CREATE PROCEDURE [dbo].[InsertNewNode] (
@new_group int, @parent_group int)
AS
DECLARE @parent int
SET @parent = (SELECT rgt
FROM ActiveDirectory.GroupRelTest
WHERE groupId = @parent_group)
UPDATE ActiveDirectory.GroupRelTest
SET lft = CASE WHEN lft > @parent
THEN lft + 2
ELSE lft END,
rgt = CASE WHEN rgt >= @parent
THEN rgt + 2
ELSE rgt END
WHERE rgt >= @parent
INSERT INTO ActiveDirectory.GroupRelTest (groupId, lft, rgt)
VALUES (@new_group, @parent, (@parent + 1))
Any help will be much appreciated.
Quote:
Originally Posted by MCrowley
If you happen to be using SQL Server 2008, why not use the hierarchyid? |
Unfortunately, I'm using SQL Server 2005.