470,594 Members | 1,536 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,594 developers. It's quick & easy.

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

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
Oct 16 '06 #1
2 12053
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
Oct 16 '06 #2
Hello

Thanks, it worked.

But I didn't get thru your language :)

Once again Thanks

Regards
Reshmi
Oct 17 '06 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by robert | last post: by
2 posts views Thread by Galina | last post: by
4 posts views Thread by M | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.