RE: Endless cycle in inserting/updating tables

From: larry elkins <elkinsl_at_flash.net>
Date: Fri, 13 Oct 2000 20:18:45 -0500
Message-Id: <10648.119239@fatcity.com>

Rick,

Like Jared, I also kind of wondered about the DB design. Then again, it seems like sometimes times when I see something "questionable", it turns out that there is a good reason, sometimes not. Since Jared has already raised the design issue, I will not go there.

Here is one approach you might want to try:

Create a package with a variable to be referenced by the triggers.
When a trigger executes, check the value of the variable. If it is null, let's say, do the following:
set it to a value
do the DML against the other tables
reset the variable back to a null (or whatever your indicator values might be)

If this approach is used in each of the triggers, the initial insert action on Table A, for example, will set the variable and insert into each of the other two tables. When the triggers for those two tables, B and C, fire, they will check the package variable, see that it is not null, and will not fire their DML against the other two tables.

Care needs to be taken in how you want to handle exceptions and the resetting of the variable. And, the values you want to use for the variable and it's "state" are up to you. In the example above, the trigger would look for a null value to know it wasn't initialized and executing. You could just as easily use a BOOLEAN, this or that, whatever you like.

If you need an example, I worked up a quick one and can send it your way. But, I think the above should be enough to get you going.

Regards,

Larry Elkins

-----Original Message-----
From: Jared Still [mailto:jkstill_at_bcbso.com] Sent: Friday, October 13, 2000 1:09 PM
To: Cale, Rick T (Richard)
Cc: oracledba_at_quickdoc.co.uk; ORACLE-L_at_fatcity.com Subject: Re: Endless cycle in inserting/updating tables

Rick,

You need to seriously consider the design of these tables and/or this process. It sounds suspect.

Jared

On Fri, 13 Oct 2000, Cale, Rick T (Richard) wrote:

> Hi All,
> I have 3 tables tab1,tab2,tab3. Each table has a trigger that will update
or
> insert the other 2
> Tab1 when inserting inserts into tab2 and tab3.
> Tab2 when inserting inserts into tab1 and tab3.
> Tab3 when inserting inserts into tab1 and tab2
>
> As you can see this would result in endless loop.
>
> Does anyone have any suggestions on how to solve?
>
> Also if a table has a foreign constraint and a before insert/update
trigger
> what is done first on insert
> or update? Is the RI constraint checked first or is the trigger fired
first.
> I have a situation if the RI
> constraint fails I want to insert a record into another table so the
foreign
> key constraint does not fail
>
> Thanks
> Rick
>
>
>
> --------
> Think you know someone who can answer the above question? Forward it to
them!
> to unsubscribe, send a blank email to oracledba-unsubscribe_at_LAZYDBA.com
> to subscribe send a blank email to oracledba-subscribe_at_LAZYDBA.com
>

Jared Still
Certified Oracle DBA and Part Time Perl Evangelist ;-) Regence BlueCross BlueShield of Oregon
jkstill_at_bcbso.com - Work - preferred address jkstill_at_teleport.com - private

Think you know someone who can answer the above question? Forward it to them!
to unsubscribe, send a blank email to oracledba-unsubscribe_at_LAZYDBA.com Received on Fri Oct 13 2000 - 20:18:45 CDT