ORA-04091 - Mutation

Posted on 1999-01-08
Medium Priority
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.
Question by:vantive

Accepted Solution

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
  -- some code
  select count(*) from K_TABLE            <- !!!!!! you cannot do it !!!!!!!
  -- some code

you neither can work with views on the table

Expert Comment

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.


Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.

Join & Write a Comment

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This article will show a step by step guide on how to mask column values in Oracle 12c using DBMS_REDACT full redaction option. This option is available on licensed Oracle Enterprise edition as part of Oracle's Advanced Security.
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 a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

597 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