Table "ParentChild" saves data in following way

ItemId    ItemName       ParentId    RelationId
-------    -------------      ----------  --------------
1           Universe          Null
2           Galaxies          1                1 .1 
3           Planets            2                1.1.1
4           Stars               2                1.1.2

like this one item can have only one parent at a time but it may have 9
children under it.

The RelationId may continue like
1.1
1.1.1
1.1.1.1.
1.1.1.1.1.
1.1.1.1.1.1
1.1.1.1.1.1.1
1.1.1.1.1.1.1.1
1.1.1.1.1.1.1.1.1

So if one item's position changes we must change its relationid and
relationids of
children under it.

For this I wrote a trigger which would get fired on update of relationid.
I wrote successful trigger but SQL Server allows it to execute 32 times only
where as it is supposed 9^9 times.

Don't use recursion, use a loop, or better: upgrade to SQL 2005 and use
common table expressions.

Expect a post from Joe Celko and the suggestion to use the nested model. :)

I've written this example that can be altered to provide what you need:
http://milambda.blogspot.com/2005/07/climbing-trees-is-for-monkeys.html


ML

---
http://milambda.blogspot.com/

convert flat file to native file format for bcp
Disappearing data (happens rarely)
Query Help: Counts & Sums
Year To Date?
trying to do some weird math in an update statement