Re: [Q] Insert to mutating table - (with a twist)

All you need to do is

• add to the package that the AFTER, FOR EACH ROW trigger populates a 'boolean' variable such as:

    IamExecuting boolean default FALSE;

• in the AFTER trigger that processes the data in the package add code to the effect:

create or replace trigger ....
AFTER insert on T
begin
  IamExecuting := TRUE;
  ....your code here
  IamExecuting := FALSE;
exception
  when others then
    IamExecuting := FALSE;
    raise;
end;

• in the AFTER, FOR EACH ROW trigger add at the beginning:

   if ( IamExecuting ) then return; end if;    .....

That will prevent the recursive firing of the triggers (not really prevent it, just short circut it). The AFTER trigger will let the AFTER FOR EACH ROW trigger that it should not fire. The exeception handler is important in the AFTER trigger, don't leave it out.   

On Fri, 26 Jul 1996 15:49:39 GMT, jb85_at_dial.pipex.com (Simon) wrote:

>Please help a poor designer out...
>
>When a row gets inserted into a table, I need to be able to calculate
>some different values, and insert about another 3 rows into the *same*
>table, based on the data originally inserted. The details aren't
>important.
>
>I am well aware that this violates the mutating trigger rules, and
>have tried using the PL/SQL table work-around suggested by Mr. Kyte
>amongst others, for which I thank you.
>
>However, when I build the triggers and package to do this (not posted
>for sake of brevity), I get a series of errors like this:
>
>ORA-01438: value larger than specified precision allows for this
>column
>ORA-06512: at "SJH.TEST_PKG", line 6
>ORA-06512: at line 2
>ORA-04088: error during execution of trigger 'SJH.APV_AI'
>ORA-06512: at "SJH.TEST_PKG", line 6
>ORA-06512: at line 2
>
>
>The ORA 04088 error is repeated several times, and then the trigger
>exits, with no action.
>
>Oracle support say that because I am inserting into the same table
>with an after insert trigger on it, that I am creating an infinite
>loop, and that this is why the trigger fails. I can quite see their
>point, but am still not entirely convinced - is there *any* way around
>this type of mutating trigger problem?
>
>It would be great to solve, as this would let me keep most of the
>business rules in the server, rather than resorting to client code.
>
>Thanks in advance; any ideas will be *much* appreciated!
>
>Simon
>--
>Simon Holt -- System Designer/DBA
>** My opinions are not nescessarily those of my employers **
>Britannia Zinc Ltd. Bristol, UK. (+44) 0117 982 3646
>

Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com                          

http://govt.us.oracle.com -- Check out our web site! Brand new, uses Oracle Web Server and Database

statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Wed Jul 31 1996 - 00:00:00 CEST