Re: Enforcing Hierarchical Relationships

Peter,  

> I have a table with a foreign key reference to itself.

[snip]

> Now I am trying to add a check on the database to not allow a circular
> reference. That is like below:
>
> emp_id name manager_id
> ------ ----------------- ----------
> 1 John Doe 3
> 2 Bill Hemmings 1
> 3 Anne Francis 2
>
> I could not figure out how to check this using a constraint. So my next
> idea was to use a trigger. I tried the following
 

[snip]

> This trigger caused a table mutating error which is consistent with the
> oracle documentation. Does anyone have any ideas on how I can implement
> this check into the database? I need to do this check whenever the
> manager_id is updated.

        All of the "for each row" triggers will set off the table mutation error, so you need to do whatever queries are necessary in a before insert
or before update (not "for each row") trigger, putting the results somewhere
that you can access them during your "for each row" triggers. I've used arrays within packages to do this, but for hierarchical information, I really
like to use SQL, so I've actually created dummy tables which I copy the table
in question into, then query it for the hierarchical relationships and test
for self-referencing "loops" caused by the new data. Creating another table
is probably not the most efficient way to do this, but it works.

        So, I'd make a table called emp_copy, and in the before insert and before update triggers I'd copy the entire emp table into emp_copy (since I
don't know what rows I'm going to need, alas). Then, in the before insert
(or update) for each row triggers, I just do the query you wanted to do, but
I do it against the emp_copy table instead of the emp table.

Tom Received on Thu May 20 1999 - 14:43:13 CDT