GeekInterview.com
Series: Subject: Topic:
Question: 285 of 490

What is mutating trigger? How do you resolve it?
If a trigger body is trying to update dependent tables, will it get a mutating error?

Asked by: Interview Candidate | Asked on: Dec 1st, 2006
Showing Answers 1 - 11 of 11 Answers
PAWAN AHUJA

Answered On : Dec 3rd, 2006

Mutating trigger is trigger that is currently being modified by DML opertion.we can resolve it by applying statement lelel trigger.RegardsPawan Ahuja

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

Answered On : Dec 8th, 2006

if the trigger attempts to select or modify the table while the trigger has not completed (ie. table is in transition). then mutating trigger error occurs. because the table is in middle of a transaction so it causes the trigger to mutate.you can change the trigger to statement level and apply the logic there.

  
Login to rate this answer.
vikramssheth

Answered On : Jan 30th, 2007

View all answers by vikramssheth

We can override this problem using temporary Table Concept.

  
Login to rate this answer.

Mutating trigger has similar concept to that of deadlock. When a table is in the half way of executing a transaction and is the owner of a trigger and if the same table is accessed by the trigger somewhere else in the same time then at that instance mutating trigger occurs.

  
Login to rate this answer.

To avoid Mutating trigger there are many ways. One of the best approach is to make use of the after trigger which clearly takes care of concurrency problems associated with mutating triggers .When after trigger is used only after the process of current updating is finished the next process gets processed.

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

If a trigger body is trying to update dependent tables the best approach in this case to avoid mutating trigger errors would be to make the transactions independent and not associated with triggers which is technically called as autonomous transactions.

Yes  2 Users have rated as useful.
  
Login to rate this answer.
Palaniraman

Answered On : Dec 11th, 2008

View all answers by Palaniraman

Mutating trigger is trigger that is currently being modified by DML opertion. For eg.,You created a trigger trigger1 on table1, it should fire after update for each row. And you wrote some update statement on the same table (table1) inside the trigger . When you execute the individual update stmt on table1, the trigger get fires and the trigger also is currently being updated the same rows in table1, which is called mutating error and mutating trigger.

  
Login to rate this answer.
ndrajit.raj

Answered On : Dec 23rd, 2008

View all answers by ndrajit.raj

Mutating table is table which is modified by any DML statement.It is inconsistent mode and we write row level trigger to solve it.

  
Login to rate this answer.
imran44

Answered On : Dec 27th, 2008

View all answers by imran44

Mutating trigger is trigger that is currently being modified by DML opertion.

  
Login to rate this answer.
sabrishandyou

Answered On : Jan 26th, 2010

View all answers by sabrishandyou

There are cases when we have mutating table error inspite of using an "AFTER TRIGGER at ROW LEVEL".  This happens when you are using an after delete trigger.  For instance a delete was issued and the trigger gets fired.at the same time it also needs the count of the records in the table.  In such a case,it gives a mutation error.  This can be solved by make it a statement level trigger.  Because in such a case, the table count is queried after the delete is fully executed.

  
Login to rate this answer.
ASHOK

Answered On : Aug 31st, 2012

MUTATING : MUTATING TRIGGER IS TRIGGER WHICH IS USED TO PERFORM DML OPERATIONS WE CAN RESOLVE BY REGARDS ASHOK

Code
  1. CREATE OR REPLACE TRIGGER TRG_UPDATE_SAL
  2. BEFORE UPDATE ON EMP
  3. FOR EACH ROW
  4. IF :NEW.SAL<:OLD.SAL THEN
  5. UPDATE EMP SET COMM =2000;
  6. ELSIF :NEW.SAL<:OLD.SAL THEN
  7. UPDATE EMP SET COMM=1000;

  
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

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.