Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

ORA-04091 - Mutation

Posted on 1999-01-08
2
Medium Priority
?
917 Views
Last Modified: 2008-03-06
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.
0
Comment
Question by:vantive
2 Comments
 
LVL 9

Accepted Solution

by:
konektor earned 200 total points
ID: 1083612
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
 
LVL 2

Expert Comment

by:dslavin
ID: 1083613
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

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

783 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question