Re: Mutating problem

Hi Philippe,

Mutaing Error Problem:-

1. You only get mutating errors from per-row triggers, not from after statement or before statement triggers (unless they are under per-row triggers).
2. You only get these when you modify a table being read or you read a table being modified.

The (unfortunate) solution is to have per-row triggers write what they see into a side table. The side table can be a PL/SQL table or an actual table on disk. Then the after statement trigger reads that data, does the changes you wanted to do originally, and clears out the side table. This will hit an infinite recursion of after-table triggers. You get around that by having the after-table trigger set a flag saying, "I am in an after-table trigger now", and unset the flag at the end. Then the same after-table trigger must (before all this) check that flag and simply return without doing anything if it is being called recursively.

Hope this helps,
Manoj Jain
Oracle DBA

In article <35371776.F14204B3_at_arpege.imaginet.fr>,   philippe <philippe_at_arpege.imaginet.fr> wrote:
>
> With the following shema, i got a mutating table problem each time I try
> to modify the
> SUIVI column of dossier. So I dropped the constraint on the SUIVI table
> and it works. It does not satisfy me anyway. What is the problem and how
> can I solve it ??
>
> create table DOSSIER (
> NODOSSIER NUMBER(38) PRIMARY KEY,
> SUIVI VARCHAR(20),
> ...)
>
> create table SUIVI (
> NODOSSIER NUMBER(38),
> SUIVI VARCHAR(20),
> DATE_DEB_SUIVI NUMBER(38),
> DATE_FIN_SUIVI NUMBER(38),
> CONSTRAINT FK_Suivi FOREIGN KEY (NODOSSIER) REFERENCES
> DOSSIER(NODOSSIER) ON DELETE CASCADE
> );
>
> CREATE OR REPLACE TRIGGER TRG_DOSSIER
> AFTER UPDATE OF SUIVI ON DOSSIER
> FOR EACH ROW
> BEGIN
> INSERT INTO SUIVI VALUES (:new.NODOSSIER, :old.SUIVI,
> :old.DATE_DEB_SUIVI, :old.DATE_FIN_SUIVI);
> END IF;
> END;
>
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Sat Apr 18 1998 - 00:26:31 CDT