Re: Enforcing Hierarchical Relationships

Thank you for the tips. I have implmented your idea with a temporary table with a "before insert or update" trigger to update the temporary table and a "before insert or update for each row" trigger to check for circular references. It works fine and since my tables are relatively small, the performance is quite OK

Pete

Tom Clymer wrote in message <374465D1.4B9510E9_at_jps.net>...
>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 - 19:39:57 CDT