Re: Trigger : table is mutating

Absolutely correct, which is why I recommended reviewing the documentation. There are many reasons not to use it, but it is there for those cases where its functionality fits the design intent.

Which brings me to the next question. If what we are trying to do is identify which row in a table is the MAIN one, why don't we have a unique indicator which points to the correct value, rather than have to worry about setting more than one value correctly. For example, assume that we have a table 'indicator_table' which has two columns, 'table_name' and 'table_row_identifier'. In this table would be a row with values '.ADRESSEFAC_CLIENT' and 'some identifier which indicates the row which is the main one'. Then the body of our trigger is something like

BEGIN
  IF UPDATING AND :NEW.main=3D'Y' THEN

      update indicator_table
          set table_row_identifier = identifier for this row being

   where table_name = 'ADRESSEFAC_CLIENT';   END IF;
END; or something like this. No mutating table, better semantics including transactional consistency, and a repeatable pattern that can be used consistently for any similar situation.

Better?

On Fri, 19 Jan 2001 06:41:23 GMT, Marc Billiet <someone.overthere_at_living_in.be> wrote:

>Yes, but if you use an autonomous transaction, you loose the whole idea =
>
>of a transaction, i.e. a rollback of the statement that caused the=20
>execution of the trigger, will not rollback the changes made by the=20
>trigger (in the autonomous transaction). If that is what you want, there=
>=20
>is no problem.
>
>Marc=20
>>>>>>>>>>>>>>>>>>> Oorspronkelijk bericht <<<<<<<<<<<<<<<<<<
>
>Op 2001-01-19, 6:34:55, schreef stan <stan.taylor_at_eds.com> over het them=
>a=20
>Re: Trigger : table is mutating:
>
>
>> Try looking up autonomous transactions in Oracle Documentation. I am
>> doing this from memory, but I think if you add
>> PRAGMA AUTONOMOUS_TRANSACTION
>> in the trigger definition before the BEGIN statement, all your
>> problems will be solved.
 

>> On Thu, 11 Jan 2001 17:09:13 +0100, "J=E9r=F4me 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 MA=
>IN=20
>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=3D'Y' THEN
>> > UPDATE AdresseFac_Client SET main=3D'N' WHERE main=3D'Y' AND
>> >ROWID!=3D:NEW.ROWID;
>> > END IF;
>> >END;
>> >
>> >What happened :
>> >- ORA-04091: table GESCO.ADRESSEFAC_CLIENT is mutating, trigger/funct=
>ion=20
>may
>> >not see it
>> >
>> >How can I deal with this problem ?
>> >
>> >Jerome
>> >
>> >
  Received on Sat Jan 20 2001 - 03:08:00 CET