ORA-04091 - Mutation

ORA-04091 - Mutation. Please, I want to make a trigger that update on cascade but this generates the error. Please try to solve this problem using a TRIGGER. OBS: I'm using AFTER UPDATE event.
vantiveAsked:
Who is Participating?
 
konektorCommented:
trigger generate mutation when you try to acces the same table on which is trigger defined

for example  :

CREATE TRIGGER trigger_on_k
AFTER INSERT ON K_TABLE
FOR EACH ROW
BEGIN
  -- some code
  select count(*) from K_TABLE            <- !!!!!! you cannot do it !!!!!!!
  -- some code
END;

you neither can work with views on the table
0
 
dslavinCommented:
The only time that we have gotten the MUTATING TRIGGER error is when a foreign key constraint (such as ON CASCADE) competes with the trigger.

The only solution that we have found for this is to implement the constraint as part of the trigger, thus:

1) Drop the constraint (normally some time of foreign key)
2) Add the code to the trigger(s) that implements the constraint:

   If, for example, it is a foreign key ON CASCADE DELETE relationship, then you will have to delete the child records in a BEFORE DELETE trigger on the parent record, etc.

If this is more helpful than konektor's comments, then please reject his answer and I can post some sample code in a more complete answer of my own.

If this is not the problem, then it might be that you are doing a TABLE update while some of the individual ROWS are in transition.  In this case you should try to modify your trigger so that it is an AFTER UPDATE ON ROW trigger.


0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.