Re: help with mutating table

In article <3694c948.2081723_at_192.86.155.100>,   tkyte_at_us.oracle.com wrote:
> A copy of this was sent to grey1969_at_my-dejanews.com
> (if that email address didn't require changing)
> On Tue, 05 Jan 1999 23:46:45 GMT, you wrote:
>
> >I'm fairly new to Oracle, and I'm having a problem figuring out how to get
> >around a mutating table. I have a before insert trigger that gets the
primary
> >key for the parent table:
> >
> > select client_seq.nextval into v_client_id from dual;
> > :new.client_id := v_client_id;
> >
> >Then I have an after insert trigger on the same table that uses the new
> >client_id and tries to insert it into a child table:
> >
> > insert into logic_table
> > (Client_id, flag_value)
> > select :new.client_id, -1 from some_table;
> >
> >The after insert trigger fails because of mutating. There's a foreign key on
> >logic_table.Client_id. The trigger works if I remove the foreign key
> >constraint.
> >
> >I thought that the insert had taken place by the time the after insert
trigger
> >fires. Why isn't the client_id available for the insert?
> >
> >Aside from removing the constraint, how can I insert a record into the child
> >table (logic_table) from a trigger?
> >
> >-----------== Posted via Deja News, The Discussion Network ==----------
> >http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
>
> The way to do this is to defer reading the table until after all of the row
> level changes have been made. Below is an example. We use a package to
> maintain a state across the triggers. the first trigger, a BEFORE trigger,
> simply resets the package state to some know state. the second trigger
collects
> all of the rowids affected by the update into a table. The third trigger
> contains all of the logic you want to perform for the affected rows. You will
> loop over the entries in the pl/sql table. My example computes the average
> salary by dept for each updated employee record.... showing you how to read
the
> table using the saved rowid.
>
> Here is an example that cascades an insert into a child table upon insert into
> the parent:
>
> SQL> create table emp ( empno int primary key, ename varchar2(25) );
>
> Table created.
>
> SQL> create table emp_child ( empno int references emp(empno) );
>
> Table created.
>
> SQL>
> SQL>
> SQL> create or replace package emp_pkg
> 2 as
> 3 type ridArray is table of rowid index by binary_integer;
> 4 rids ridArray;
> 5 cnt number;
> 6 end;
> 7 /
>
> Package created.
>
> SQL>
> SQL> create or replace trigger emp_bu
> 2 before insert on emp
> 3 begin
> 4 emp_pkg.cnt := 0;
> 5 end;
> 6 /
>
> Trigger created.
>
> SQL>
> SQL> create or replace trigger emp_aufer
> 2 after insert on emp
> 3 for each row
> 4 begin
> 5 emp_pkg.cnt := emp_pkg.cnt + 1;
> 6 emp_pkg.rids( emp_pkg.cnt ) := :new.rowid;
> 7 end;
> 8 /
>
> Trigger created.
>
> SQL>
> SQL> create or replace trigger emp_au
> 2 after insert on emp
> 3 declare
> 4 emp_row emp%rowtype;
> 5 begin
> 6 for i in 1 .. emp_pkg.cnt loop
> 7 select * into emp_row from emp where rowid =
> emp_pkg.rids(i);
> 8
> 8 insert into emp_child values ( emp_row.empno );
> 9 end loop;
> 10 end;
> 11 /
>
> Trigger created.
>
> SQL>
> SQL> insert into emp values ( 1, 'me' );
>
> 1 row created.
>
> SQL>
> SQL> select * from emp;
>
> EMPNO ENAME
> ---------- -------------------------
> 1 me
>
> SQL> select * from emp_child;
>
> EMPNO
> ----------
> 1
>
> SQL>
>
> Thomas Kyte
> tkyte_at_us.oracle.com
> Oracle Service Industries
> Reston, VA USA
>
> --
> http://govt.us.oracle.com/ -- downloadable utilities
>
> ----------------------------------------------------------------------------
> Opinions are mine and do not necessarily reflect those of Oracle Corporation
>
> Anti-Anti Spam Msg: if you want an answer emailed to you,
> you have to make it easy to get email to you. Any bounced
> email will be treated the same way i treat SPAM-- I delete it.
>

I see what you've done, and I have a couple of questions. The first question is that you seem to be using two after insert triggers. I'm using Oracle 7.5 and I was under the impression that you could only have one type of any trigger per table, that is, one before insert, one after insert, etc. Is that not accurate?

My other question, is that it seems like we are doing basically the same thing. I don't understand why your solution works using an array, and my solution doesn't work using the :new operator. Both solutions take place in the after insert trigger.

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Wed Jan 06 1999 - 11:48:21 CST