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

errorsoracleplsqltrigger

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!

Best Answer

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.

Related Solutions

NOT getting a mutating table error in trigger

There is an exception. When you define a before insert, row-level trigger on a table and issue a single row INSERT statement, the table is mutating error will not be raised. But if you define the same kind of trigger and issue a multi-row INSERT statement, the error will be raised. Here is an example:

SQL> create table TB_TR_TEST(
  2    col1 number,
  3    col2 number
  4  )
  5  ;

Table created

SQL> create or replace trigger TR_TB_TR_TEST
  2  before insert on TB_TR_TEST
  3  for each row
  4  begin
  5    SELECT max(col1) + 1 INTO :NEW.col1
  6      FROM TB_TR_TEST;
  7    UPDATE TB_TR_TEST SET col2 = 5000;
  8  end;
  9  /

Trigger created

Here is a single-row insert statement, which won't raise mutating table error:

SQL> insert into TB_TR_TEST(col1, col2) values(1,2);

1 row inserted

SQL> insert into TB_TR_TEST(col1, col2) values(3,5);

1 row inserted

SQL> commit;

Commit complete

Here is a multi-row insert statement, which will raise mutating table error:

SQL> insert into TB_TR_TEST(col1, col2)
  2    select 1, 2
  3      from dual;

insert into TB_TR_TEST(col1, col2)
  select 1, 2
    from dual

ORA-04091: table HR.TB_TR_TEST is mutating, trigger/function may not see it
ORA-06512: at "HR.TR_TB_TR_TEST", line 2
ORA-04088: error during execution of trigger 'HR.TR_TB_TR_TEST'

Error in trigger: table is mutating

Replace

select portfoliono into mportfoliono from sipholder where accno=:new.accno;

with

mportfoliono := new.portfoliono;

no need to use a SELECT for values of the updated row.