Merge dentro de un trigger
Hola Mario, no puedes hacer algo sobre la misma tabla en la cual se dispara el trigger, por este motivo oracle lo detecta como error y te notifica que la tabla esta mutando, "la tabla esta cambiando en ese instante" .
Te paso esta nota :
Primero comento un poco qué es una “tabla mutante“. Se considera “mutante” una tabla que está siendo modificada, por ejemplo la tabla sobre la que el propio trigger se ha disparado por causa de un update/insert/delete.
O lo que es lo mismo, dentro de un trigger no podemos hacer referencia a la misma tabla que lo ha disparado, ya que, al estar siendo modificada, Oracle no puede asegurar una visión “consistente” de sus datos. En caso de hacerlo aparece este error.
Correcto, ¿y qué podemos hacer? Pues hay varias soluciones:
Hasta la versión 10gR2 podíamos:
Cambiar el código y no usar triggers: Podría ser que se pudiese solucionar modificando el diseño del modelo de datos o reprogramando una parte del codigo.
Usar un trigger “AFTER”: En “AFTER” los cambios en la tabla ya estan “consumados” y podemos acceder a una visión “consistente” de ésta.
Usar “PRAGMA AUTONOMOUS_TRANSACTION”: Nuestro trigger se ejecuta en una transacción diferente, y por tanto vemos la tabla en modo “consistente”. No es muy recomendable usar este sistema, ya que por ejemplo:
Si queremos realizar varios cambios seguidos dentro de la misma transacción no tendremos acceso a ellos (es una transacción diferente cada vez)
O si el trigger falla y se hace rollback tampoco nos enteraremos (no deshará la transacción en que se ha disparado el trigger)
A partir de la versión 11gR1, a las anteriores opciones podemos añadir:
Usar “COMPOUND TRIGGERS”: En la versión 11gR1 ha aparecido un nuevo tipo de trigger llamado “Compound Triggers“. En estos triggers podemos realizar cálculos previos para definir un “estado” que después es accesible durante la ejecución del trigger.
En resumen, que los cálculos que queramos hacer sobre la tabla afectada por el trigger los haremos previamente, guardando los valores necesarios en variables a las que accederemos posteriormente (evitando el acceso directo a la tabla y por tanto el problema). No nos soluciona todos los casos de tablas mutantes pero sí una parte importante de ellos.
El ejemplo que viene en la documentación es bastante bueno, por lo que os lo referencio directamente: Example 9-4 Compound Trigger that Avoids Mutating-Table Error
PD: No dejéis de estudiar qué otras ventajas ofrecen los “compound triggers”, pues la solución a las tablas mutantes sólo es una consecuencia de sus funcionalidades
Otra nota de interés :
Using Compound Triggers to Avoid Mutating-Table Error
You can use compound triggers to avoid the mutating-table error (ORA-04091) described in Trigger Restrictions on Mutating Tables.
Scenario: A business rule states that an employee's salary increase must not exceed 10% of the average salary for the employee's department. This rule must be enforced by a trigger.
Solution: Define a compound trigger on updates of the table hr.employees, as in Example 9-4. The state variables are initialized each time the trigger fires (even when the triggering statement is interrupted and restarted).
Example 9-4 Compound Trigger that Avoids Mutating-Table Error
CREATE OR REPLACE TRIGGER Check_Employee_Salary_Raise
FOR UPDATE OF Salary ON Employees
COMPOUND TRIGGER
Ten_Percent CONSTANT NUMBER := 0.1;
TYPE Salaries_t IS TABLE OF Employees.Salary%TYPE;
Avg_Salaries Salaries_t;
TYPE Department_IDs_t IS TABLE OF Employees.Department_ID%TYPE;
Department_IDs Department_IDs_t;
TYPE Department_Salaries_t IS TABLE OF Employees.Salary%TYPE
INDEX BY VARCHAR2(80);
Department_Avg_Salaries Department_Salaries_t;
BEFORE STATEMENT IS
BEGIN
SELECT AVG(e.Salary), NVL(e.Department_ID, -1)
BULK COLLECT INTO Avg_Salaries, Department_IDs
FROM Employees e
GROUP BY e.Department_ID;
FOR j IN 1..Department_IDs.COUNT() LOOP
Department_Avg_Salaries(Department_IDs(j)) := Avg_Salaries(j);
END LOOP;
END BEFORE STATEMENT;
AFTER EACH ROW IS
BEGIN
IF :NEW.Salary - :Old.Salary >
Ten_Percent*Department_Avg_Salaries(:NEW.Department_ID)
THEN
Raise_Application_Error(-20000, 'Raise too big');
END IF;
END AFTER EACH ROW;
END Check_Employee_Salary_Raise;
Saludos
Luis
