| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: help with mutating table
grey1969_at_my-dejanews.com wrote:
>
> 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
Multi-triggers
You can have multiple triggers of the same "type" on a table - I think it came in around 7.1 when Oracle needed this to add replication triggers to tables without interfering with ones that the user has created...Ensure that your "compatible" parameter is set and you should be alright...
:New
..is only applicable inside a row level trigger. Once you exit the row trigger either by moving to the "after statement trigger" or return to the calling environment, then you don't have access to this value - hence the PL/SQL table as Thomas suggests...
HTH --
 |
 |