Re: Trigger : table is mutating

In article <93klsf$ba5$1_at_wanadoo.fr>,   "Jérôme PERRET" <jperret_at_freesurf.fr> wrote:
> Hi,
>
> What I have :
> - A table with a column named MAIN
>
> What I want :
> - Only one record is the main one so only one record as the column
 MAIN set
> to Y (Yes). Others are set to N (No)
>
> What I tried :
> - A trigger on the table (BEFORE UPDATE FOR EACH ROW) :
>
> BEGIN
> IF UPDATING AND :NEW.main='Y' THEN
> UPDATE AdresseFac_Client SET main='N' WHERE main='Y' AND
> ROWID!=:NEW.ROWID;
> END IF;
> END;
>
> What happened :
> - ORA-04091: table GESCO.ADRESSEFAC_CLIENT is mutating,
 trigger/function may
> not see it
>
> How can I deal with this problem ?
>
> Jerome
>
>

The problem is you are going to be updating the same table hence the mutation error.

You might want to make the update of the main field a separate procedure, essentially setting all the fields to 'N' then setting the one row to 'Y'. I would believe either a before or after trigger will give the same error. A separate procedure would be logical, in that you most likely are not going to set the main='Y' that often. I would suspect this is a once-in-awhile maintenance problem.

--
Michael Krolewski
Rosetta Inpharmatics
mkrolewski_at_rii.com
              Usual disclaimers


Sent via Deja.com
http://www.deja.com/