Solved

ORA-04091 - Mutation

Posted on 1999-01-08
2
896 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 100 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
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.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

790 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