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.