Este problema me ocurrió cuando estaba tratando de realizar un trigger para eliminar registros cuando se actualiza o elimina un registro padre dentro de la misma tabla. Por ejemplo, teniendo una tabla «clientes» que usa una relación interna id_cliente y id_cliente_referido, si el id_cliente se actualizaba o eliminaba debía cambiar/eliminar el id del referido también.
Este error se genera en triggers al tratar de manipular una tabla que está siendo modificada o va a ser modificada, limitando nuestras acciones, según investigué hay varias soluciones y varias formas de que ocurra el error. Esto puede ocurrir al hacer alguna operación de lectura «select» en la misma tabla que está eliminándose o actualizándose un registro al mismo tiempo, o al realizar operaciones de escritura «insert,update,delete», también puede ocurrir si el trigger se vuelve a ejecutar causando bucle, es decir, tienes un trigger que elimina y al ejecutar un «delete» en la misma tabla, volverá a ejecutar el trigger. Ese mismo era mi caso y Oracle lo advierte con ese error.
Soluciones:
Usar subquery
Una solución si lo que estás realizando es un select, por ejemplo, select/delete es utilizar un subquery, ya que primero ejecuta el select y luego el delete.
|
1 |
DELETE FROM clientes WHERE id_cliente IN (SELECT id_cliente FROM clientes WHERE nombre = 'Juan') |
Usar variables o crear tabla temporal
En caso de que sea más complejo o necesites toda la información del select para ejecutar otro query, deberás realizar primero el select, guardar el resultado en alguna variable con la sentencia BULK COLLECT, para luego utilizarla libremente. También se podría crear una tabla temporal para mayor manipulación.
Usar procedimientos
Los trigger y funciones tienen ciertas limitaciones de lo que puedes realizar allí y con un procedimiento tendrás más libertad, creas el trigger y luego llamas al procedimiento dentro del trigger, ejecutándolo como transacción aparte.
Usar transacciones autónomas
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
CREATE OR REPLACE TRIGGER eliminar_referido AFTER DELETE OR UPDATE ON CLIENTES FOR EACH ROW DECLARE PRAGMA AUTONOMOUS_TRANSACTION; --Activar para transacciones autonomas v_id_cliente NUMBER; BEGIN v_id_cliente := :OLD.v_id_cliente; IF UPDATING -- Si esta actualizando THEN IF (:OLD.v_id_cliente != :NEW.v_id_cliente) THEN BEGIN DELETE FROM clientes WHERE id_cliente_referido = v_id_cliente; COMMIT; DBMS_OUTPUT.PUT_LINE('CAMBIO EL CLIENTE. SE ELIMINA TODOS LOS REFERIDOS'); END; END IF; END IF; IF DELETING -- Si esta eliminando THEN BEGIN DELETE FROM clientes WHERE id_cliente_referido = v_id_cliente; COMMIT; DBMS_OUTPUT.PUT_LINE('ELIMINO EL CLIENTE. SE ELIMINA TODOS LOS REFERIDOS'); END; END IF; END; |
Yo tuve que utilizar transacciones autónomas para evitar el error, este tiene sus ventas y desventajas. Puedes utilizarlo como una ejecución independiente en su propio bloque, lo que te da más libertad para manipular datos. La gran desventaja de este método, es que, si es una transacción independiente, se ejecutará y hará la eliminación aunque la transacción padre falle y el rollback revertirá solo la padre. Hay que tener cuidado con esto y siempre probar y probar para evitar futuros errores.
