GeekInterview.com
Series: Subject: Topic:
Question: 189 of 292

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

Asked by: be17be | Member Since Nov-2006 | Asked on: Dec 1st, 2006

View all questions by be17be   View all answers by be17be

Showing Answers 1 - 6 of 6 Answers
joseph0609

Answered On : Dec 5th, 2006

View all answers by joseph0609

 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. 

  
Login to rate this answer.
joseph0609

Answered On : Dec 5th, 2006

View all answers by joseph0609

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 . 

  
Login to rate this answer.

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

Yes  1 User has rated as useful.
  
Login to rate this answer.

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

  
Login to rate this answer.
Kranthi

Answered On : 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.

  
Login to rate this answer.
Ashish

Answered On : 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.

  
Login to rate this answer.

Give your answer:

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

Related Open Questions

Ads

Connect

twitter fb Linkedin GPlus RSS

Ads

Question Categories

Oracle DBA FAQ

Interview Question

 Ask Interview Question?

 

Latest Questions

Interview & Career Tips

Get invaluable Interview and Career Tips delivered directly to your inbox. Get your news alert set up today, Once you confirm your Email subscription, you will be able to download Job Inteview Questions Ebook . Please contact me if you there is any issue with the download.