0

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

2
  • Which part of the error cause explanation seems unclear?
    – mustaccio
    May 30 '15 at 18:43
  • Which table was mutating, you don't show us the actual error message, rather an explanation of it. May 31 '15 at 9:56
1

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.