jeudi 1 janvier 2015

How to Avoid Mutating Error in Oracle


Iam Using Oracle 11g can any one help me,


i have a trigger like this


create or replace TRIGGER TRG_UPD_RAPRJCT11 AFTER UPDATE OF CURRENT_STATUS ON AGL_RAPROJECT FOR EACH ROW BEGIN


IF (:OLD.CURRENT_STATUS='Review2' and :NEW.CURRENT_STATUS='Review3')then


INSERT INTO a_audit_service ( raproject_id, assesment_name, assest_id, asset_name, auditproject_id, program_name, rauserinput_id, find_name, string1, string2, string3, string4, string5, raworkflow_id )


SELECT rp.raproject_id,rp.version AS assesment_name,rp.asset_id,a.name AS asset_name, rp.auditproject_id ,ap.name AS program_name,ri.rauserinput_id,g.name AS find_name, ac.string1,ac.string2,ac.string3,ac.string4 ,ac.string5, rp.raworkflow_id


FROM agl_raproject rp INNER JOIN agl_asset a ON (rp.asset_id=a.asset_id) INNER JOIN agl_auditproject ap ON (ap.auditproject_id=rp.auditproject_id) LEFT JOIN agl_rauserinput ri ON (ri.raproject_id=rp.raproject_id) LEFT JOIN agl_gap g ON (g.rauserinput_id=ri.rauserinput_id) LEFT JOIN agl_customattributes ac ON (ac.customattributes_id=ri.customattributes_id) WHERE rp.raproject_id=:new.raproject_id;


end if; end;


So it is created successfully. But When I run the Below Update Statement .


UPDATE agl_raproject SET current_status='Review3' WHERE current_status='Review2' and raproject_id=405323966463354641;


I got Below Error..


SQL Error: ORA-04091: table PSOWNER.AGL_RAPROJECT is mutating, trigger/function may not see it ORA-06512: at "PSOWNER.TRG_UPD_RAPRJCT11", line 5 ORA-04088: error during execution of trigger 'PSOWNER.TRG_UPD_RAPRJCT11' 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.


How to Avoid this Please Help me Thanks in Advance.





Aucun commentaire:

Enregistrer un commentaire