Evitar el error Oracle ORA-04091 (tabla mutante)

Este error ocurre usualmente al realizar una consulta de un campo desde un trigger y ese campo es el que hace, que salte el trigger.

Vamos a ver un ejemplo, que produce este error:

-- Creamos una tabla
CREATE TABLE usuarios
(id Number,
nombre VARCHAR2(10));

-- Insertamos registros de prueba
INSERT INTO usuarios values (1, 'Pepe');
INSERT INTO usuarios values (2, 'Juan');
INSERT INTO usuarios values (3, 'Luis');
COMMIT;

-- Creamos un trigger
CREATE OR REPLACE TRIGGER TAU_usuarios
    AFTER UPDATE OF nombre ON usuarios
    FOR EACH ROW
DECLARE
    v_Contador NUMBER;
BEGIN
    SELECT count(*)
      INTO v_Contador
      FROM usuarios
     WHERE nombre = 'Luis';

    DBMS_OUTPUT.PUT_LINE(‘El número de Luises son: ‘ || v_Contador);
END;

Y ahora actualizamos el campo que es consultado en el trigger:

UPDATE usuarios 
   SET nombre = 'Luis' 
 WHERE id = 1;

Y nos salta el error de tabla mutante:

ERROR en línea 1:
ORA-04091: la tabla usuarios está mutando, puede que el disparador/la función no puedan verla
ORA-06512: en "USER.TAU_USUARIOS", línea 5
ORA-04088: error durante la ejecución del disparador 'USER.TAU_USUARIOS'

SOLUCIONES A LOS ERRORES TABLA MUTANTE (ORA-04091)

SOLUCIÓN 1: EVITAR TRATAMIENTO A NIVEL DE FILA

En vez de tratar a nivel de registro, se pudiera tratar a nivel de sentencia evitando poner «FOR EACH ROW», se solventaría el problema. Claro está, siempre que lo que se pretenda con el trigger este cubierto con esta menor granularidad.

Quedaría de la siguiente manera:

CREATE OR REPLACE TRIGGER TAU_usuarios
     AFTER UPDATE OF nombre ON usuarios
DECLARE
    v_Contador NUMBER;
BEGIN
   SELECT count(*)
     INTO v_Contador
     FROM usuarios
    WHERE nombre = 'Luis';
    
    DBMS_OUTPUT.PUT_LINE(‘El número de Luises son: ‘ || v_Contador);
END;

Actualizamos otra vez:

UPDATE usuarios 
   SET nombre = 'Luis' 
 WHERE id = 1;
El número de Luises son: 2

1 fila actualizada.

Como vemos finaliza correctamente. Ha finalizado además indicando correctamente el número de Luises totales a 2, que son los que hay al final en la tabla.

SOLUCIÓN 2: TRANSACCIONES AUTÓNOMAS

Las transacciones autónomas es una forma de aislar distintas transacciones.
Comprobamos como está actualmente la tabla

select * from usuarios;

        ID NOMBRE
---------- ----------
         1 Luis
         2 Juan
         3 Luis

Creamos el trigger.

CREATE OR REPLACE TRIGGER TAU_usuarios
    AFTER UPDATE OF nombre ON usuarios
    FOR EACH ROW
DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;
    v_Contador NUMBER;

BEGIN
   SELECT count(*)
     INTO v_Contador
     FROM usuarios
    WHERE nombre = 'Luis';

    DBMS_OUTPUT.PUT_LINE(‘El número de Luises son: ‘ || v_Contador);
END;

Lanzamos el UPDATE:

UPDATE usuarios 
   SET nombre = 'Carmen' 
 WHERE id IN (1, 2);
El número de Luises son: 2
El número de Luises son: 2

2 filas actualizadas.

Pero si nos fijamos en los datos de la tabla:

select * from usuarios;

        ID NOMBRE
---------- ----------
         1 Carmen
         2 Carmen
         3 Luis

En el resultado de la traza al hacer el UPDATE aparecían 2 Luises, que son los que había iniciales y no contabilizando los registros actualizados. Por otro lado aparece dos veces, porque se han actualizado dos registros. Por lo que hay que tener mucho cuidado si se utiliza esta solución y darse cuenta de lo que podría ocurrir.

SOLUCIÓN 3: TRIGGERS COMPUESTOS (ORACLE 11G)

Esta opción sólo es válida a partir de la versión 11G y es la solución más sencilla y elegante.

CREATE OR REPLACE TRIGGER TRG_COMPUESTO
        FOR UPDATE  ON usuarios
        COMPOUND TRIGGER

    v_Contador NUMBER;

    -- Se lanzará después de cada fila actualizada
    AFTER EACH ROW IS
    BEGIN
        dbms_output.put_line('Se realizó la actualización');
    END AFTER EACH ROW;

    -- Se lanzará después de la sentencia
    AFTER STATEMENT IS
    BEGIN
       SELECT count(*)
         INTO v_Contador
         FROM usuarios
        WHERE nombre = 'Luis';

        DBMS_OUTPUT.PUT_LINE(‘El número de Luises son: ‘ || v_Contador);
    END AFTER STATEMENT;
END TRG_COMPUESTO;

Vamos a ver el contenido de la tabla:

SELECT *  FROM usuarios;

        ID NOMBRE
---------- ----------
         1 Carmen
         2 Carmen
         3 Luis

Vamos a actualizar:

El número de Luises son: 1
Se realizó la actualización
El número de Luises son: 1
Se realizó la actualización
El número de Luises son: 2

2 filas actualizadas.

Y el estado final:

SELECT *  FROM usuarios;

    ID NOMBRE
------ ----------
     1 Luis
     2 Carmen
     3 Luis

En la actualización nos aparece previamente a cada registro la cantidad de Luises, que había y después de la actualización se ve el total de Luises actualizados.