Tabla mutando. ORA-04091

ORA-04091: table XXX is mutating, trigger/function may not see it


En el caso de tener dos triggers sobre la misma tabla nos podemos encontrar con el error ORA-04091.

El trigger1 actualiza campos de la tabla XXX, y el trigger2 accede a información de XXX. Esta situación provoca el error de tabla mutando. El trigger2 intenta accede a una información que se está generando, es decir, la tabla está mutando, cambiando o actualizandose.

Para solucionar el problema debemos aplazar las acciones del trigger2 hasta que se hayan completado las acciones de triger1.


Ejemplo.

Trigger1 realiza UPDATE sobre XXX
Trigger2 realiza SELECT sobre XXX

Para la select del trigger2 debemos utilizar los rowid de las filas afectadas para acceder a los valores :new.


Implementación:

SQL> create table parent
2 ( theKey int primary key,
3 status varchar2(1),
4 effDate date
5 )
6 /
Table created.

SQL> create table log_table
2 ( theKey int references parent(theKey),
3 status varchar2(1),
4 effDate date
5 )
6 /
Table created.

SQL> REM this package is used to maintain our state. We will save the rowids of newly
SQL> REM inserted / updated rows in this package. We declare 2 arrays -- one will
SQL> REM hold our new rows rowids (newRows). The other is used to reset this array,
SQL> REM it is an 'empty' array

SQL> create or replace package state_pkg
2 as
3 type ridArray is table of rowid index by binary_integer;
4
4 newRows ridArray;
5 empty ridArray;
6 end;
7 /
Package created.

SQL> REM We must set the state of the above package to some known, consistent state
SQL> REM before we being processing the row triggers. This trigger is mandatory,
SQL> REM we *cannot* rely on the AFTER trigger to reset the package state. This
SQL> REM is because during a multi-row insert or update, the ROW trigger may fire
SQL> REM but the AFTER tirgger does not have to fire -- if the second row in an update
SQL> REM fails due to some constraint error -- the row trigger will have fired 2 times
SQL> REM but the AFTER trigger (which we relied on to reset the package) will never fire.
SQL> REM That would leave 2 erroneous rowids in the newRows array for the next insert/update
SQL> REM to see. Therefore, before the insert / update takes place, we 'reset'

SQL> create or replace trigger parent_bi
2 before insert or update on parent
3 begin
4 state_pkg.newRows := state_pkg.empty;
5 end;
6 /
Trigger created.

SQL> REM This trigger simply captures the rowid of the affected row and
SQL> REM saves it in the newRows array.

SQL> create or replace trigger parent_aifer
2 after insert or update of status on parent for each row
3 begin
4 state_pkg.newRows( state_pkg.newRows.count+1 ) := :new.rowid;
5 end;
6 /
Trigger created.

SQL> REM this trigger processes the new rows. We simply loop over the newRows
SQL> REM array processing each newly inserted/modified row in turn.

SQL> create or replace trigger parent_ai
2 after insert or update of status on parent
3 begin
4 for i in 1 .. state_pkg.newRows.count loop
5 insert into log_table
6 select theKey, status, effDate
7 from parent where rowid = state_pkg.newRows(i);
8 end loop;
9 end;
10 /
Trigger created.

SQL> REM this demonstrates that we can process single and multi-row inserts/updates
SQL> REM without failure (and can do it correctly)

SQL> insert into parent values ( 1, 'A', sysdate-5 );
1 row created.

SQL> insert into parent values ( 2, 'B', sysdate-4 );
1 row created.

SQL> insert into parent values ( 3, 'C', sysdate-3 );
1 row created.

SQL> insert into parent select theKey+6, status, effDate+1 from parent;
3 rows created.

SQL> select * from log_table;

THEKEY S EFFDATE
---------- - ---------
1 A 04-AUG-99
2 B 05-AUG-99
3 C 06-AUG-99
7 A 05-AUG-99
8 B 06-AUG-99
9 C 07-AUG-99

6 rows selected.

SQL> update parent set status = chr( ascii(status)+1 ), effDate = sysdate;
6 rows updated.

SQL> select * from log_table;

THEKEY S EFFDATE
---------- - ---------
1 A 04-AUG-99
2 B 05-AUG-99
3 C 06-AUG-99
7 A 05-AUG-99
8 B 06-AUG-99
9 C 07-AUG-99
1 B 09-AUG-99
2 C 09-AUG-99
3 D 09-AUG-99
7 B 09-AUG-99
8 C 09-AUG-99
9 D 09-AUG-99

12 rows selected.


Enlace: http://asktom.oracle.com/tkyte/Mutate/index.html