What is a mutating trigger error? How can we resolve it?I need this reply asap. Thanks

Questions by be17be   answers by be17be

Showing Answers 1 - 6 of 6 Answers

joseph0609

  • Dec 5th, 2006
 

 MUTATING TRIGGER ERROR IS NOTHING BUT MUTATING TABLE ERROR.

FIRST WHAT IS MUTATION WHY IT HAPPENS?

FOR EXAMPLE  A TRIGGER IS WRITTEN (BEFORE UPDATING A RECORD CHECK FOR VALIDATION OF THAT VALUE) WHILE  A UPDATE STATEMENT IS WRITTEN EXPLICITLY THEN  TRIGGER IS EXECUTED IF THE TRIGGER KEEPING THE SELECT STMT THERE THE PROBLEM OCCURS THIS PROBLEM IS TRIGGER MUTATION  HERE ORACLE PL/SQL ENGINE TRYS TO UPDATE THE RECORD VALUE AND SIMULTANEOUSLY WE ARE READING THE RECORD VALUE INSIDE THE TRIGGER USING A SELECT STMT HERE ORACLE PL/SQL ENGINE WILL BE IN A BIT OF CONFUSION WHICH VALUE TO UPDATE  WHETHER WHICH VALUE TO UPDATE . WHILE SELECT STMT  THE TABLE WILL BE LOCKED. 

  Was this answer useful?  Yes

joseph0609

  • Dec 5th, 2006
 

WE CAN RESOLVE IT BY NOT KEEPING A SELECT STMT INSIDE A TRIGGER IN MUTATING TRIGGER  WE ARE SIMULTANEOUSLY READING THE VALUE AND WANTS TO UPDATE IT. SO WE BYPASS  BY  READING THE VALUE WITH A SEPERATE VARIABLE  IN A PROCEDURE  ,SO BY READING THE OLD VALUE THROUGH THIS VARIABLE. AFTER STORING THE VALUE IN THAT VARIABLE WE ARE GOING FRO UPDATION . 

  Was this answer useful?  Yes

WHEN WE ARE UPDATING A TABLE AND AT THE SAME TIME TRYING TO RETRIEVE  DATA FROM THAT TABLE.  IT WILL RESULT INTO MUTTAING TABLE AND IT WILL RESULT INTO MUTATING ERROR.

WE CAN RESOLVE IT BY DECLARING 'PRAGMA AUTONOMUS TRANSACTION' IN DECLARATIVE SECTION OF THE BLOCK

Kranthi

  • Jul 13th, 2011
 

In before update/delete trigger if we use select statement for fetching the records.in this scenario will wet mutating trigger error.using row level trigger we can avoid instead of statement level trigger. we can accurate results.

  Was this answer useful?  Yes

Ashish

  • Mar 24th, 2012
 

See, in oracle to maintain read consistency oracle keeps a copy in undo section of database for the data which is under modification by some transaction. And so readers do not wait for writers and can read the previous data from this section until it is not committed by writers. So my question is why we can not retrieve data in this fashion in case of this also . After all we are only reading the data in triggering body.

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

Answer Question

Click here to Login / Register your free account


 
Send   Reset

 

Related Answered Questions

 

Related Open Questions