Posted by Hugo Kornelis on 05/27/05 02:00

On 26 May 2005 00:49:31 -0700, coosa wrote:

>Any way it's solved now and i shall post for Edward and others who
>might be interested:
(snip)

Hi coosa,

There are still a few problems with your code:

1. It won't work properly on multi-row inserts;
2. If you change a row, the calculated depth will be wrong for all
dependant rows.

Instead of storing a derived result, it's usually much better to
calculate it when querying the data. In this case, the easiest solution
is probably a user-defined function that traverses the tree to find the
depth (much like the algorithm you now use in your trigger). Then, you
can call that UDF when querying the data.

Another option would be to reconsider the model you have chosen to use
to store the tree. Your model is called the adjacency list model, and
it's commonly used because it is very intuitive. But several other
models are better suited for use in a relational database. The nested
sets model is the best known alternative. If you google for nested sets
model, you'll probably find some good examples. (Or you can buy a copy
of Trees and Hierarchies in SQL, by Joe Celko).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Back to original message]