Re: Enforcing hierarchial tree integrity

From: Peter Griffin <peter_at_guy.com>
Date: Fri, 29 Jul 2005 17:54:41 GMT
Message-ID: <B3uGe.196146$on1.63300@clgrps13>

Sybrand Bakker wrote:
> On Thu, 28 Jul 2005 20:20:09 GMT, Peter Griffin <peter_at_guy.com> wrote:
>
>

>>Basically, when a node is deleted then all of it's children (if any) 
>>should get deleted too. I suspect this needs to be done using a delete 
>>trigger.

>
>
> Just create the constraint with on delete cascade.
> That is all there is to it.

The hierarchical data in the table is self-referencing, the queries are generated via the hierarchical query extensions START WITH and CONNECT BY.

For the purpose of demonstration suppose I have this tree structure:

NODE_ID PARENT_NODE_ID NODE
------- -------------- ---------

       1	          NULL NODE 1 (root)
       2		     1 NODE 2
       3	             1 NODE 3
       4	             1 NODE 4
       5	             3 NODE 5

If I go and delete NODE 3 it orphans NODE 5 and tree integrity is corrupted... I now have two root nodes instead of one. What I'd like to accomplish is either: a) automatically delete the child record(s) of a node when it is deleted; or b) throw an exception such as "violation: node has children."

It's simple enough to do on the client-side which is how our current implementation does it, but I'd really rather enforce this on the Oracle-side instead for 100% integrity.

Any suggestions on how to go about doing this on the server-side? Received on Fri Jul 29 2005 - 12:54:41 CDT