I don't know how to fix ORA-04091 mutating trigger error

Question

I'm learning Oracle SQL, but when dealing with triggers I'm finding an error, that prevents me from updating values in that table.

The error is:

error ORA-04091: table USUARIO.LINEAS is mutating, trigger/function may not see it ORA-06512: at "USUARIO.TRG_ACTUALIZARPEDIDO", line 5 ORA-04088: error during execution of trigger 'USUARIO.TRG_ACTUALIZARPEDIDO'

I've learning about it, but can't find a solution or understand by myself what's the problem.

CREATE OR REPLACE TRIGGER trg_actualizarpedido
AFTER INSERT OR UPDATE OR DELETE ON LINEAS
FOR EACH ROW

DECLARE
    sumaImporteLineas NUMBER := 0;

BEGIN
    SELECT SUM(IMPORTE) INTO sumaImporteLineas FROM LINEAS WHERE NUMPEDIDO = :NEW.NUMPEDIDO;
    UPDATE PEDIDOS ped SET ped.TOTAL = sumaImporteLineas WHERE ped.NUM = :NEW.NUMPEDIDO;
END;

Thanks!

Answer 1

ORA-04091

This is Oracle's way of saying:

you really don't want to do that.

Here is my short-list of workarounds.

Option 1

Use a VIEW.

This is the preferred method.

Option 2

Use a Materialized View.

Yes, you can Fast Refresh SUM() on commit.

Option 3

Use a TAPI (Table API). (a Package)

All DML operations must go through this Package; never against the actual table.

Option 4

3-Trigger solution. (This could be a single Complex trigger.)

Without proper locking, you could have "lost updates".

Even with proper locking, Dead Locks could occur.

Answer 2

Within a trigger reacting to a change to a table, you cannot query the table itself. But you don't need to.

It looks as though you're trying to keep a running total based on changes to the value of IMPORTE. For that, you need not one but three triggers:

/* Insert - Add New value */ 
CREATE OR REPLACE TRIGGER trg_actualizarpedido1
AFTER INSERT ON LINEAS
FOR EACH ROW
BEGIN
    UPDATE PEDIDOS 
    SET TOTAL = TOTAL + :NEW.IMPORTE 
    WHERE NUMPEDIDO = :NEW.NUMPEDIDO ; 
END;

/* Update - Subtract the Old value, Add New value */ 
CREATE OR REPLACE TRIGGER trg_actualizarpedido2
AFTER UPDATE ON LINEAS
FOR EACH ROW
BEGIN
    UPDATE PEDIDOS 
    SET TOTAL = TOTAL - :OLD.IMPORTE + :NEW.IMPORTE 
    WHERE NUMPEDIDO = :NEW.NUMPEDIDO ; 
END;

/* Delete - Subtract the Old value */ 
CREATE OR REPLACE TRIGGER trg_actualizarpedido3
AFTER DELETE ON LINEAS
FOR EACH ROW
BEGIN
    UPDATE PEDIDOS 
    SET TOTAL = TOTAL - :OLD.IMPORTE 
    WHERE NUMPEDIDO = :OLD.NUMPEDIDO ; 
END;

Answer 3

As already given in the other answers using a trigger for such task is not the preferred way of doing it. For the sake of completeness here a trigger based solution (not tested):

create or replace trigger trg_actualizarpedido
  for insert or update or delete on LINEAS 
  COMPOUND TRIGGER

  type NUMPEDIDO_t is table of LINEAS.NUMPEDIDO%type;
  NUMPEDIDOs NUMPEDIDO_t ;
  sumaImporteLineas NUMBER;

  before statement is 
  begin
    NUMPEDIDOs := NUMPEDIDO_t();
  end before statement;

  after each row is 
  begin
    NUMPEDIDOs.EXTEND;
    IF INSERTING OR UPDATING ('NUMPEDIDO') THEN
       NUMPEDIDOs(NUMPEDIDOs.LAST) := :NEW.NUMPEDIDO;
    ELSIF DELETING THEN
       NUMPEDIDOs(NUMPEDIDOs.LAST) := :OLD.NUMPEDIDO;
    END IF;
  end after each row;

  after statement is 
  begin      
    for i in NUMPEDIDOs.FIRST..NUMPEDIDOs.LAST LOOP
       SELECT SUM(IMPORTE) INTO sumaImporteLineas 
       FROM LINEAS 
       WHERE NUMPEDIDO = NUMPEDIDOs(i);

       UPDATE PEDIDOS ped SET ped.TOTAL = sumaImporteLineas 
       WHERE ped.NUM = NUMPEDIDOs(i);
    end loop;          
  end after statement;

end;

Answer 4

You can not query the table that caused a trigger to fire inside the trigger itself. But in this case, I don't see any need to. You can use :NEW to access the values you're selecting from the table instead: