Trying to read a table on Trigger during update, giving mutation error

Question

Before updating a table I have to insert in another table, but to accomplish that I need information from the table (and other tables) which I'm updating, trying to read my updating table in the trigger is giving an error

ERROR at line 1: ORA-04091: table BASEDATOS2.SOL_SOLICITUD_PREMIOS is mutating, trigger/function may not see it ORA-06512: at "BASEDATOS2.INS_AFTER_UPD_PRICES", line 10 ORA-04088: error during execution of trigger 'BASEDATOS2.INS_AFTER_UPD_PRICES'

Here´s my trigger

    CREATE OR REPLACE TRIGGER INS_AFTER_UPD_PRICES BEFORE UPDATE OF CONCEDIDO ON SOL_SOLICITUD_PREMIOS 
FOR EACH ROW WHEN (new.CONCEDIDO = 'S')
DECLARE
    DESDE DATE;
    HASTA DATE;
    MONTO NUMBER;
    MAX_ID NUMBER;
    v_mov_premio NUMBER;
    CUENTA NUMBER;
BEGIN
    SELECT MAX(ID_MOVIMIENTO)+1 INTO MAX_ID FROM AHO_MOVIMIENTOS_CUENTA;
    SELECT SSP.FECHA_DESDE,SSP.FECHA_HASTA,SE.MONTO_COBERTURA INTO DESDE,HASTA,MONTO
    FROM SOL_SOLICITUD_PREMIOS SSP JOIN SOL_EVENTOS SE
    ON SE.COD_EVENTO = SSP.COD_EVENTO 
    WHERE SE.COD_EVENTO=:OLD.COD_EVENTO
    AND SSP.ID=:OLD.ID;

    select id_tipo into v_mov_premio
            from aho_tipo_movimiento
            where UPPER(nombre_tipo) like '%PAGO DE PREMIO/SUBSIDIO%';

    SELECT AM.ID_CUENTA INTO CUENTA 
    FROM AHO_MOVIMIENTOS_CUENTA AM
    JOIN AHO_TIPO_MOVIMIENTO ATM ON ATM.ID_TIPO=AM.ID_TIPO
    WHERE ATM.ID_TIPO='A' AND ROWNUM=1;

    INSERT INTO AHO_MOVIMIENTOS_CUENTA 
    VALUES(MAX_ID, SYSDATE, MONTO*(HASTA-DESDE),v_mov_premio,CUENTA );
END;

Anyone knows how can I make it work?

Answer 1

The problem here is that your trigger is defined on SOL_SOLICITUD_PREMIOS, and in the body of the trigger you're SELECTing data from SOL_SOLICITUD_PREMIOS. In ROW triggers you're not allowed to read, insert, update, or delete other rows in the table which the trigger is defined on because it could cause a trigger loop, where the database gets stuck evaluating recursive invocations of the same trigger. However, in this case reading data from SOL_SOLICITUD_PREMIOS is not needed because you already have the data you need available to you in the :OLD pseudo-row. If I'm reading things correctly you can rewrite your trigger as follows and it should do what you're looking for:

CREATE OR REPLACE TRIGGER INS_AFTER_UPD_PRICES
  BEFORE UPDATE OF CONCEDIDO ON SOL_SOLICITUD_PREMIOS 
  FOR EACH ROW
  WHEN (new.CONCEDIDO = 'S')
DECLARE
    MONTO         NUMBER;
    MAX_ID        NUMBER;
    v_mov_premio  NUMBER;
    CUENTA        NUMBER;
BEGIN
    SELECT MAX(ID_MOVIMIENTO)+1 INTO MAX_ID FROM AHO_MOVIMIENTOS_CUENTA;

    SELECT SE.MONTO_COBERTURA
      INTO MONTO
      FROM SOL_EVENTOS SE 
      WHERE SE.COD_EVENTO = :OLD.COD_EVENTO;

    select id_tipo
      into v_mov_premio
      from aho_tipo_movimiento
      where UPPER(nombre_tipo) like '%PAGO DE PREMIO/SUBSIDIO%';

    SELECT AM.ID_CUENTA
      INTO CUENTA 
      FROM AHO_MOVIMIENTOS_CUENTA AM
      JOIN AHO_TIPO_MOVIMIENTO ATM
        ON ATM.ID_TIPO = AM.ID_TIPO
      WHERE ATM.ID_TIPO = 'A' AND
            ROWNUM = 1;

    INSERT INTO AHO_MOVIMIENTOS_CUENTA 
    VALUES(MAX_ID,
           SYSDATE,
           MONTO * (:OLD.FECHA_HASTA - :OLD.FECHA_DESDE),
           v_mov_premio,
           CUENTA );
END INS_AFTER_UPD_PRICES;

Best of luck.