Tablas mutantes… que no mutan!

Aunque el nombre que Oracle da a estas tablas (‘Mutating Tables’, Tablas Mutantes) parece más propio de una película de George A. Romero, el concepto es más sencillo (y menos terrorífico) que todo eso.

“A mutating table is a table that is currently being modified by an update, delete, or insert statement. When a trigger tries to reference a table that is in state of flux (being changed), it is considered
“mutating” and raises an error since Oracle should not return data that has not yet reached its final state”

Una tabla mutante es una tabla que está siendo modificada. Si un ‘trigger’ hace referencia a la propia tabla sobre la que se dispara se producirá una excepción con error ORA-04091.

Según esto, cualquier ‘trigger’ que haga una simple consulta sobre la tabla que lo dispara generará dicho ORA-04091.

Vamos a verlo.

SQL*Plus: Release 10.2.0.1.0 - Production on Jue Feb 8 10:08:18 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Introduzca el nombre de usuario: carlos@test10g
Introduzca la contraseña:

Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

carlos@TEST10G> select * from prueba01;

        ID TXT
---------- -------------------------
         1 UNO
         2 DOS
         3 TRES
         4 CUATRO

carlos@TEST10G> create trigger BID_PRUEBA01_TRG
  2  BEFORE INSERT OR DELETE
  3  ON PRUEBA01
  4  FOR EACH ROW
  5  DECLARE
  6       i_dummy NUMBER;
  7  BEGIN
  8     SELECT MAX(ID)
  9       INTO i_dummy
 10       FROM PRUEBA01;
 11  END BI_PRUEBA01_TRG;
 12  /

Disparador creado.

Verificamos el comportamiento esperado borrando una fila:

carlos@TEST10G> delete from prueba01 where id=5;
delete from prueba01 where id=5
            *
ERROR en línea 1:
ORA-04091: la tabla CARLOS.PRUEBA01 está mutando, puede que el disparador/la función no puedan verla
ORA-06512: en "CARLOS.BID_PRUEBA01_TRG", línea 4
ORA-04088: error durante la ejecución del disparador 'CARLOS.BID_PRUEBA01_TRG'

carlos@TEST10G>

Hasta aquí todo ocurre como se espera.

Pero, veamos que ocurre con un INSERT:

carlos@TEST10G> INSERT INTO PRUEBA01 VALUES (5, 'CINCO');

1 fila creada.

¿Cómo? ¿Por qué? ¡La ejecución del trigger debería dar el mismo error que en el caso del DELETE!

Pero la inserción se ha efectuado:

carlos@TEST10G> SELECT * FROM PRUEBA01;

        ID TXT
---------- -------------------------
         1 UNO
         2 DOS
         3 TRES
         4 CUATRO
         5 CINCO

carlos@TEST10G>

Veamos que ocurre si insertamos una fila, pero de otra manera:

carlos@TEST10G> INSERT INTO PRUEBA01 SELECT 6,'SEIS' FROM DUAL;
INSERT INTO PRUEBA01 SELECT 6,'SEIS' FROM DUAL
            *
ERROR en línea 1:
ORA-04091: la tabla CARLOS.PRUEBA01 está mutando, puede que el disparador/la función no puedan verla
ORA-06512: en "CARLOS.BI_PRUEBA01_TRG", línea 4
ORA-04088: error durante la ejecución del disparador 'CARLOS.BI_PRUEBA01_TRG'

carlos@TEST10G>

Así pues, sólo en el caso de INSERT’s de una sola fila con VALUES, el ORA-04091 no se produce.

Pero, vayamos un paso más allá: ¿Qué pasará con operaciones ‘BULK INSERT’ que también tienen ‘VALUES’?

carlos@TEST10G> DECLARE
  2     TYPE T_T_PRUEBA01 IS TABLE OF PRUEBA01%ROWTYPE INDEX BY BINARY_INTEGER;
  3     T_PRUEBA01 T_T_PRUEBA01;
  4  BEGIN
  5     T_PRUEBA01(1).ID:= 6;
  6     T_PRUEBA01(1).TXT:= 'SEIS';
  7     T_PRUEBA01(2).ID:= 7;
  8     T_PRUEBA01(2).TXT:= 'SIETE';
  9
 10     FORALL i IN T_PRUEBA01.FIRST..T_PRUEBA01.LAST
 11        INSERT INTO PRUEBA01 VALUES T_PRUEBA01(i); --'VALUES' sin paréntesis!!
 12  END;
 13  /
DECLARE
*
ERROR en línea 1:
ORA-04091: la tabla CARLOS.PRUEBA01 está mutando, puede que el disparador/la función no puedan verla
ORA-06512: en "CARLOS.BID_PRUEBA01_TRG", línea 4
ORA-04088: error durante la ejecución del disparador 'CARLOS.BID_PRUEBA01_TRG'
ORA-06512: en línea 10

Como vemos, aquí se vuelve a dar el error (como era de esperar).

Pero, una vez más ¿y si la tabla sólo tiene una fila?

carlos@TEST10G> DECLARE
  2     TYPE T_T_PRUEBA01 IS TABLE OF PRUEBA01%ROWTYPE INDEX BY BINARY_INTEGER;
  3     T_PRUEBA01 T_T_PRUEBA01;
  4  BEGIN
  5     T_PRUEBA01(1).ID:= 6;
  6     T_PRUEBA01(1).TXT:= 'SEIS';
  7
  8     FORALL i IN T_PRUEBA01.FIRST..T_PRUEBA01.LAST
  9        INSERT INTO PRUEBA01 VALUES T_PRUEBA01(i); --'VALUES' sin paréntesis!!
 10  END;
 11  /

Procedimiento PL/SQL terminado correctamente.

system@TEST10G>

Aunque sea una operación BULK INSERT, la inserción de una sola fila es análoga al caso de INSERT INTO …VALUES simple.

Saludos.

Carlos.