My PARTNER table has the following structure:

P_CODE INTEGER PK
PARENT_CODE INTEGER
NAME CHAR(40)
...
...
ETC,ETC.

The PARENT_CODE of course references the P_CODE in the same table.
Before insert You can simply select the parent(s) of the partner being
inserted: SELECT * FROM PARTNERS WHERE P_CODE=NEW.PARENT_KOD or select
the children (if any) of the partner being inserted: SELECT * FROM
PARTNERS WHERE PARENT_CODE=:P_CODE. However my GUI forces the user to
insert only the correct treenodes. I use TTreeView with objects in the
nodes, that stores the actual (P_CODE-PARENT_CODE) values, so I don't
have to check any hierarchical rules on the DB level between records.

The loop won't be infinite because the IF statement - based on the
existance of child partners - controls the recursive execution of the
SP. If the given partner doesn't have any sub-partners then the SP won't
be executed. If it has then it will be executed, but on the top level
only selects the sub-partners examinded in the previous SP call. So
there is an ENTRY-LEVEL-CONDITION for calling recursively the SP instead
of EXIT-CONDITION. However I don't know your table's structure but with
my structure shown above the SP works perfectly and there is no infinite
loop.

Regards: Alex