Re: Mutating Table Insert Situation

In article <352223C3.D3597979_at_si.unirioja.es>,   Julio <julio.negueruela_at_si.unirioja.es> wrote:
>
> drpaner_at_intrex.net escribió:
> >
> > Hello all,
> >
> > I have two tables, a Master and a Detail. There is a foreign key
 relationship
> > between the two.
> >
> > When I Update Master records, I have a trigger ( Update, After ) that
 deletes
> > all of the Detail records and then will attempt to insert new Detail
 records
> > based on the :NEW. column values.
> >
> > I am successful in deleting the Detail records, however, when I attempt to
> > Insert into the Detail table, I receive the Mutating Table error.
> >
> > I am NOT performing any Select statements against either table. Just a
 simple
> > Insert statement.
> >
> > I suspect that Oracle performs a Select statement on the Master table
> > behing-the-scenes to verify the foreign key integrity and that is causing
 the
> > error. I have read the newsgroups buy did not see anyone coming up with
 this
> > error without performing a Select statement in the trigger.
>
> I suspect so. May be Oracle performs this select as it was another
> transaction.
> In this case you could try to perform a commit after deleting rows in
> detail columns, so that Oracle can see the changes.
>
> > Does anyone know of a workaround on this? Any help would be greatly
> > appreciated!
> >
> > Thank you,
> > Daniel
>
> Regards.
> --
> Julio Negueruela
> DBA Servicio Informático
> Universidad de La Rioja - Spain
> Telf: 941-299179 Fax: 941- 299180
> mailto:julio.negueruela_at_si.unirioja.es
>

Yes, Oracle indeed uses a SELECT-statement to check referential integrity. You will have to drop the R/I-constraints and program their logic by hand. Reference: Application Developer's Guide, Chapter "Using Triggers" (sp.?)

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Fri Apr 03 1998 - 00:00:00 CST