RE: Triggers recursive call

From: Diana Duncan <Diana_at_fileFRENZY.com>
Date: Mon, 27 Nov 2000 13:58:31 -0500
Message-Id: <10693.122980@fatcity.com>

What you have to do is the following:

1. Write a package to contain a PL/SQL table of records that you are dealing with. Include a package boolean variable named "cascading" and set it to FALSE.
2. Write your "for each row" trigger, but instead of doing the recursive processing, just: If not cascading then
3. Set cascading to TRUE
4. Add the row information as an entry to the package PL/SQL table
5. Write an after table trigger which If cascading then
6. Loops through the entries in the PL/SQL table, doing the recursive processing for each one
7. Removes the entries from the PL/SQL table.
8. Sets cascading to FALSE

That's the generic method for resolving mutating tables errors. However, I am curious about your example -- it seems to make no sense. What is it you are trying to do?

Regards,
Diana

-----Original Message-----
From: Jordi Sanmarti [mailto:JSanmarti_at_tss.com.pe] Sent: Wednesday, November 22, 2000 7:35 PM To: Multiple recipients of list ORACLE-L Subject: Triggers recursive call

        Hi to all,

        Does anybody know how setup the database to avoid any trigger recursive call?

	Look at this dummy example:
	Trigger is "statement" related type trigger (not of "for each row"
type).
	If I run the below using "for each row" clause I get mutating error.

	create or replace trigger tr_booking after update on booking
	declare
	  cont number;
	begin
	  ...
	  update booking set col1 = col1 + 10 where col2 = 'A';
	  ...
	end;
	/

	TIA,
	Jordi

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Jordi Sanmarti
  INET: JSanmarti_at_tss.com.pe

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 also send the HELP command for other information (like subscribing). Received on Mon Nov 27 2000 - 12:58:31 CST