On Wed, 19 Aug 1998 14:32:48 -0500, "Robert Goodwin"
<robertgoodwin_at_yahoo.com> wrote:
>Okay, I'm familiar with all the normal ways to get a mutating table error,
>but here's a new one:
>
- snip ---
>On table T1, we have a trigger defined as follows:
>CREATE OR REPLACE TRIGGER t1_trigger
>BEFORE INSERT OR UPDATE
>ON T1 FOR EACH ROW
>BEGIN
> IF INSERTING THEN
> :new.field_1 := UPPER(:new.field_1);
> END IF;
> ....
>END;
>
>Now, when we execute an UPDATE on table T2, we get a mutating table error on
>table T2. However, if we remove the IF INSERTING THEN block from
>t1_trigger, we no longer get the mutating table error. Since we are
>updating and not inserting, it seems that this block should make no
>difference. But it is definitely the cause of the mutating table error.
>
It looks like Oracle decides about possible 'mutating tables' for this
trigger at compiletime and not at runtime. Since t1_trigger is an
update trigger and it modifies primary key, T2 migth be a mutating or
constraining table for this trigger. So, it may not be invoked when
T2 is mutating. I added av few 'put_line' to t1_trigger and I don't
think any single line of t1_trigger is ever executed.
Would this be different on pre 7.3?
Possible workaround: split your trigger in two, one for update
and the other for insert.
--
Regards,
Roland
Received on Thu Aug 20 1998 - 13:32:42 CDT
