I don't know how to fix ORA-04091 mutating trigger error 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!
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.