Re: Trigger : table is mutating

Create an UNIQUE index on the main column and a check constraint allowing 'Y' and NULL and you won't have this issue at all, at least not in this particular case.
For generic solutions to ORA-4091 http://osi.oracle.com/~tkyte

Regar
"Jérôme PERRET" <jperret_at_freesurf.fr> wrote in message news:93klsf$ba5$1_at_wanadoo.fr...
> 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
>
>
Received on Thu Jan 11 2001 - 18:58:08 CET