joseph0609

 Profile Answers by 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. 

joseph0609

 Profile Answers by 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 . 

shravanam

 Profile Answers by shravanam Questions by shravanam

• Oct 24th, 2007

 

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

xxx123

 Profile Answers by xxx123 Questions by xxx123

• Feb 24th, 2011

 

While updating any table from that table dont use SELECT statement inside the trigger.

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.

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.