RE: Endless cycle in inserting/updating tables

From: Diana Duncan <Diana_at_fileFRENZY.com>
Date: Fri, 13 Oct 2000 20:56:29 -0400
Message-Id: <10648.119238@fatcity.com>

I have to agree with Jared. However, if you still really need to do this, you need to put conditions on when the updates and inserts take place - for instance, if Tab1 was the original table, and it performs inserts into Tab2 and Tab3, the Tab2 and Tab3 triggers need to know not to fire their own inserts. You can use packaged variables to keep track. See below:

create package trigPkg is

        cascading boolean;
end trigPkg;
/
create package body trigPkg is
begin

        cascading := FALSE;
end trigPkg;
/
create trigger air_tab1
after insert on tab1
for each row
begin

	if not trigPkg.cascading then
		trigPkg.cascading := TRUE;
		insert into tab2....
		insert into tab3....
		trigPkg.cascading := FALSE;
	end if;

end air_tab1;
/
create trigger air_tab2
after insert on tab2
for each row
begin

	if not trigPkg.cascading then
		trigPkg.cascading := TRUE;
		insert into tab1....
		insert into tab3....
		trigPkg.cascading := FALSE;
	end if;

end air_tab2;
/

etc.....

IRT your question about the FK constraints - I can't say for sure, but it is my experience that the constraints are not checked until after the triggers have fired. So, the best thing to do if you want to never allow parent keys that don't exist would be to check for the existence of the parent in the trigger and insert it there. But, this will give you mutating tables errors unless you disable the FK constraints! Catch-22. I must say that I think this is a bad idea -- have the application code do the checks and inserts. Constraints keep your DB sane. ;-)

Diana

-----Original Message-----
From: Jared Still [mailto:jkstill_at_bcbso.com] Sent: Friday, October 13, 2000 6:06 PM
To: Multiple recipients of list ORACLE-L 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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jared Still
  INET: jkstill_at_bcbso.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may

Received on Fri Oct 13 2000 - 19:56:29 CDT