ERROR :Trigger is mutating, trigger/function may not see it

[Reshmi Jacob]

Hello,

Can any one help me in creating a trigger to update system date into a table while inserting a record into that table. I tried it like this, it is showing error !!!


The following error has occurred:

ORA-04091: table ACG.CENTREMST is mutating, trigger/function may not see it
ORA-06512: at "ACG.CENTREMST_INSERT", line 5
ORA-04088: error during execution of trigger 'ACG.CENTREMST_INSERT'

The TRIGGER I created like this....

CREATE OR REPLACE TRIGGER CentreMst_insert
AFTER INSERT on Centremst FOR EACH ROW
DECLARE
vStmt VARCHAR2(200);
CKEY VARCHAR2(15);
BEGIN
SELECT :new.cocode||TO_CHAR(:new.CentreType)||:new.Centre code INTO CKEY FROM CENTREMST;
vStmt := 'Update CentreMst Set CreatedDt = SysDate Where Cocode||CENTRETYPE||CentreCode='||CKEY;
EXECUTE IMMEDIATE vStmt ;
END;

Regards
Reshmi

[rcampos]

Usted no puede ejecutar transacciones sobre la misma tabla en un trigger.

Si usted necesita modificar la fecha del registro que se esta insertando (Col. CreatedDt) el trigger deberia ser:

CREATE OR REPLACE TRIGGER CentreMst_insert
BEFORE INSERT on Centremst
REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW FOR EACH ROW
BEGIN
:NEW.CreatedDt := Sysdate;
END;

Hello,

Can any one help me in creating a trigger to update system date into a table while inserting a record into that table. I tried it like this, it is showing error !!!


The following error has occurred:

ORA-04091: table ACG.CENTREMST is mutating, trigger/function may not see it
ORA-06512: at "ACG.CENTREMST_INSERT", line 5
ORA-04088: error during execution of trigger 'ACG.CENTREMST_INSERT'

The TRIGGER I created like this....

CREATE OR REPLACE TRIGGER CentreMst_insert
AFTER INSERT on Centremst FOR EACH ROW
DECLARE
vStmt VARCHAR2(200);
CKEY VARCHAR2(15);
BEGIN
SELECT :new.cocode||TO_CHAR(:new.CentreType)||:new.Centre code INTO CKEY FROM CENTREMST;
vStmt := 'Update CentreMst Set CreatedDt = SysDate Where Cocode||CENTRETYPE||CentreCode='||CKEY;
EXECUTE IMMEDIATE vStmt ;
END;

Regards
Reshmi

[Reshmi Jacob]

Hello

Thanks, it worked.

But I didn't get thru your language :)

Once again Thanks

Regards
Reshmi