Oracle trigger error: table %s.%s is mutating, trigger/function may not see it

errorsoracletrigger

I am having some issues while trying to use triggers over tables where I need to get the reference of custom data type from a table. I can't seem to get it to work, here are the scripts I am using right now for the affected tables.

Tables code:

LINEA_FACTURA table:

--------------------------------------------------------
--  DDL for Table LINEA_FACTURA
--------------------------------------------------------

  CREATE TABLE "ADMIN"."LINEA_FACTURA" OF "ADMIN"."T_LINEA_FACTURA" 
 OIDINDEX  ( PCTFREE 10 INITRANS 2 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ) 
 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
--------------------------------------------------------
--  DDL for Index SYS_C0010540
--------------------------------------------------------

  CREATE UNIQUE INDEX "ADMIN"."SYS_C0010540" ON "ADMIN"."LINEA_FACTURA" ("SYS_NC_OID$") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
--------------------------------------------------------
--  Constraints for Table LINEA_FACTURA
--------------------------------------------------------

  ALTER TABLE "ADMIN"."LINEA_FACTURA" ADD UNIQUE ("SYS_NC_OID$")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE;
  ALTER TABLE "ADMIN"."LINEA_FACTURA" MODIFY ("CODIGO" NOT NULL ENABLE);
--------------------------------------------------------
--  DDL for Trigger ACTUALIZAR_PRECIO_TRG
--------------------------------------------------------

  CREATE OR REPLACE EDITIONABLE TRIGGER "ADMIN"."ACTUALIZAR_PRECIO_TRG" 
AFTER INSERT ON LINEA_FACTURA 
FOR EACH ROW
BEGIN
  UPDATE ARTICULO 
  SET 
    PRECIO = :NEW.PRECIO_UNITARIO, 
    USUARIO_ACT = USER, 
    FECHA_ACT = SYSDATE
  WHERE
    CODIGO = DEREF(:NEW.ARTICULO).CODIGO;
END ACTUALIZAR_PRECIO_TRG;
/
ALTER TRIGGER "ADMIN"."ACTUALIZAR_PRECIO_TRG" ENABLE;
--------------------------------------------------------
--  DDL for Trigger LINEA_FACTURA_TRG
--------------------------------------------------------

  CREATE OR REPLACE EDITIONABLE TRIGGER "ADMIN"."LINEA_FACTURA_TRG" 
    BEFORE INSERT ON ADMIN.LINEA_FACTURA 
    FOR EACH ROW 
BEGIN
  <<COLUMN_SEQUENCES>>
  BEGIN
    IF :NEW.CODIGO IS NULL THEN
      SELECT LINEA_FACTURA_SEQ.NEXTVAL INTO :NEW.CODIGO FROM DUAL;
    END IF;
  END COLUMN_SEQUENCES;
END; 
/
ALTER TRIGGER "ADMIN"."LINEA_FACTURA_TRG" ENABLE;

ARTICULO table:

--------------------------------------------------------
--  DDL for Table ARTICULO
--------------------------------------------------------

  CREATE TABLE "ADMIN"."ARTICULO" OF "ADMIN"."T_ARTICULO" 
 OIDINDEX  ( PCTFREE 10 INITRANS 2 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ) 
 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" 
 NESTED TABLE "ARRAY_REF_T_COMPONENTE" STORE AS "ARRAY_REF_T_COMPONENTE"
 (PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ) RETURN AS VALUE;
--------------------------------------------------------
--  DDL for Index SYS_C0010513
--------------------------------------------------------

  CREATE UNIQUE INDEX "ADMIN"."SYS_C0010513" ON "ADMIN"."ARTICULO" ("SYS_NC0000500006$") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
--------------------------------------------------------
--  DDL for Index SYS_C0010514
--------------------------------------------------------

  CREATE UNIQUE INDEX "ADMIN"."SYS_C0010514" ON "ADMIN"."ARTICULO" ("SYS_NC_OID$") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
--------------------------------------------------------
--  Constraints for Table ARTICULO
--------------------------------------------------------

  ALTER TABLE "ADMIN"."ARTICULO" ADD UNIQUE ("SYS_NC_OID$")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE;
  ALTER TABLE "ADMIN"."ARTICULO" ADD UNIQUE ("ARRAY_REF_T_COMPONENTE")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE;
  ALTER TABLE "ADMIN"."ARTICULO" MODIFY ("CODIGO" NOT NULL ENABLE);
--------------------------------------------------------
--  DDL for Trigger ARTICULO_TRG
--------------------------------------------------------

  CREATE OR REPLACE EDITIONABLE TRIGGER "ADMIN"."ARTICULO_TRG" 
    BEFORE INSERT ON ADMIN.ARTICULO 
    FOR EACH ROW 
BEGIN
  <<COLUMN_SEQUENCES>>
  BEGIN
    IF :NEW.CODIGO IS NULL THEN
      SELECT ARTICULO_SEQ.NEXTVAL INTO :NEW.CODIGO FROM DUAL;
    END IF;
  END COLUMN_SEQUENCES;
END; 
/
ALTER TRIGGER "ADMIN"."ARTICULO_TRG" ENABLE;

Trigger code:

create or replace 
TRIGGER ACTUALIZAR_PRECIO_TRG 
AFTER INSERT ON LINEA_FACTURA 
FOR EACH ROW
BEGIN
  UPDATE ARTICULO 
  SET 
    PRECIO = :NEW.PRECIO_UNITARIO, 
    USUARIO_ACT = USER, 
    FECHA_ACT = SYSDATE
  WHERE
    CODIGO = DEREF(:NEW.ARTICULO).CODIGO;
END ACTUALIZAR_PRECIO_TRG;

Error message:

04091. 00000 -  "table %s.%s is mutating, trigger/function may not see it"
*Cause:    A trigger (or a user defined plsql function that is referenced in
           this statement) attempted to look at (or modify) a table that was
           in the middle of being modified by the statement which fired it.
*Action:   Rewrite the trigger (or function) so it does not read that table.

Here is what I have tried so far:
– Store the DEREF into a variable
– Remove auto-increment trigger/sequence

Best Answer

Well it was a dumb mistake by be, the solution was as simple as declaring a variable and storing a SELECT DEREF(:NEW.ARTICULO).CODIGO FROM DUAL; into it.