Updating the lineage of the tree structure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Updating the lineage of the tree structure

Author

Topic

Limbic
Starting Member

15 Posts

Posted - 2006-06-07 : 09:36:23

Hi all,
I have a table that stores a tree structure.
like
id, parentId, depth, lineage

there are two triggers behind the table, one for an insert and one for an update. they are used calculate the lineage.

Everthing works fine except when you try to assign a unit as a parentId which is actually a child from that unit.
To make it clearer here the DDL
btw. I got the idea from this article[url]http://www.developerfusion.co.uk/show/4633/3/[/url]

DROP table units
CREATE TABLE units
(
	unitId int,
	unitName nvarchar(50),
	parentId INT,
	depth INT,
	lineage NVARCHAR(255) 
)

create   TRIGGER units_InsertTrigger ON dbo.Units
FOR INSERT AS
UPDATE child SET depth = ISNULL(parent.depth + 1,0),
    lineage = ISNULL(parent.lineage,'/') + LTrim(Str(child.unitId)) + '/'
FROM units child INNER JOIN inserted i ON i.unitId=child.unitId
LEFT OUTER JOIN Units parent ON child.parentId=parent.unitId


create   TRIGGER Units_UpdateTrigger
ON dbo.Units
FOR UPDATE AS
IF UPDATE (parentId)
UPDATE child
    SET depth = child.depth - old.depth + ISNULL(parent.depth + 1,0),
    lineage = ISNULL(parent.lineage,'/') + LTrim(Str(old.unitId)) + '/' +
                  right(child.lineage, len(child.lineage) - len(old.lineage))
FROM Units child
INNER JOIN inserted old ON child.lineage LIKE old.lineage + '%'
LEFT OUTER JOIN Units parent ON old.parentId=parent.unitId


INSERT INTO units(unitId, unitName, parentid)
VALUES(1, 'parent', null)

INSERT INTO units(unitId, unitName, parentid)
VALUES(2, 'child', 1)

select * from units

1	parent	NULL	0	/1/
2	child	1	1	/1/2/

so this makes sense

but when i execute this query


UPDATE units set parentId = 2 WHERE unitId = 1


1	parent	2	2	/1/2/1/
2	child	1	3	/1/2/1/2/

here it starts to get the wrong way.

What I'm trying to do is, to check while updating the datarow if the new parent is not the child of the updated unit, and when then updating the parentId of the new parent to next available parent in the tree structure, if none exist the parentId is null or 0.

so the result should look like that