Oracle 04091 Error
Contents |
SQL TuningSecurityOracle UNIXOracle LinuxMonitoringRemote supportRemote plansRemote servicesApplication Server ApplicationsOracle FormsOracle PortalApp UpgradesSQL ServerOracle ConceptsSoftware SupportRemote Support Development Implementation Consulting StaffConsulting PricesHelp Wanted! Oracle PostersOracle Books Oracle Scripts Ion Excel-DB Don ora-04091 table is mutating trigger/function may Burleson Blog
Ora-04091 Solution
Fix Oracle mutating trigger table errors Oracle Database Tips by Burleson Consulting A mutation table is mutating trigger in oracle 10g with example defined as a table that is changing. But in dealing with triggers, it is a table that has the possibility of changing. What this means to a trigger is that if mutating trigger in oracle 11g the trigger reads a table, it can not change the table that it read from. This does not impact the exclusive use of :OLD and :NEW. It says that if the trigger reads the table (such as using a SELECT query), that changes (even using :NEW) will fail. This can also happen when a trigger on a parent table causes an insert
Oracle Statement Level Trigger
on a child table referencing a foreign key. Mutating Tables Each new release of the Oracle database reduces the impact of the mutating table error on triggers and they are much less of a problem with Oracle9i and above. If a trigger does result in a mutating table error, the only real option is to rewrite the trigger as a statement-level trigger. Mutating table errors only impact row level triggers. But to use a statement level trigger, some data may need to be preserved from each row, to be used by the statement level trigger. This data can be stored in a PL/SQL collection or in a temporary table. A simple row level trigger that causes a mutating table error can result in a very complicated statement level trigger to achieve the needed result. Here are some important items to remember about triggers. On insert triggers have no :OLD values. On delete triggers have no :NEW values. Triggers do not commit transactions. If a transaction is rolled back, the data changed by the trigger is also rolled back. Commits, rollbacks and save point
- 8:40 pm UTC Category: SQL*Plus � Version: 8.1.7 Whilst you are here, check out some content from the AskTom team: Datapump cleanup Latest Followup You oracle mutating trigger pragma autonomous transaction Asked hello, i've got a table MRC and a trigger on it oracle instead of trigger (AFTER INSERT) thus, after an insert in the table MRC, this trigger has to determine if a new
Ora-04091 After Insert Trigger
line must be inserted into an other table PLAN : for that, it does compare the :new values with the MOST RECENT enregistrement of MRC but i got a mutating http://www.dba-oracle.com/t_avoiding_mutating_table_error.htm table error i understand the problem but how can i get over ?? thanks Arnaud and we said... My personal opinion -- when I hit a mutating table error, I've got a serious fatal flaw in my logic. Have you considered the multi-user implications in your logic? Two people inserting at the same time (about the same time). What https://asktom.oracle.com/pls/apex/f?p=100:11:0%3A%3A%3A%3AP11_QUESTION_ID:9579487119866 happens then??? Neither will see eachothers work, neither will block -- both will think "ah hah, I am first"... anyway, you can do too much work in triggers, this may well be that time -- there is nothing wrong with doing things in a more straightforward fashion (eg: using a stored procedure to implement your transaction) but if you persist, you can use the technique: http://asktom.oracle.com/~tkyte/Mutate/index.html to avoid the mutating table constraint -- but I would avoid the situation that gets me there in the first place. The logic is a whole lot more understandable that way (and maintainable and testable and everything) Reviews Write a Review Ora-4091 May 05, 2003 - 5:45 pm UTC Reviewer: A reader We create trigger in the test server (8i) its working without error, and when we created at life (8) we get the following error: ORA-04091: table XXXX is mutating, trigger/function may not see it. Followup May 05, 2003 - 8:31 pm UTC they relaxed some of the constraining rules between 8.0 and 8.1 -- things are
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company http://stackoverflow.com/questions/6915325/oracle-after-update-trigger-solving-ora-04091-mutating-table-error Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, http://dba.stackexchange.com/questions/5432/what-are-the-causes-and-solutions-for-mutating-table-errors just like you, helping each other. Join them; it only takes a minute: Sign up ORACLE After update trigger: solving ORA-04091 mutating table error up vote 2 down vote favorite I am trying to create a trigger: mutating trigger create or replace trigger NAME_OF_TRIGGER after insert or update on table1 REFERENCING OLD AS OLD NEW AS NEW for each row to fill in automatically a couple of non obligatory fields when updating/inserting on a table. This requires me to use a cursor that selects from table2 and also table1 (the subject of the trigger). Is there any way to avoid the mutating table error without using a temporary table for values or an mutating trigger in autonomous transaction? sql oracle plsql triggers mutating-table share|improve this question edited Aug 2 '11 at 16:54 APC 87.3k1384184 asked Aug 2 '11 at 16:26 JoséNunoFerreira 150111 add a comment| 1 Answer 1 active oldest votes up vote 7 down vote accepted "Is there any way to avoid the mutating table error without using a temporary table for values or an autonomous transaction?" tl;dr no. The mutating table error is caused by querying the table which owns the trigger, or tables which are involved in a foreign key relationship with the owning table (at least in older versions of the database, not sure whether it still obtains). In a properly designed application this should not be necessary. This is why many people regard mutating tables as an indicator of poor data modelling. For instance, mutation is often associated with insufficient normalisation. To paraphrase Jamie Zawinski: Some people, when confronted with a mutating table exception, think "I know, I'll use autonomous transactions." Now they have two problems. Sometimes the error can be avoided by simply modifying the :NEW values in a BEFORE INSERT OR UPDATE trigger or by using virtual columns. But you'll need to post more details to see whether these apply. But the best workaround is not to need any other kind. share|improve this answer edited Aug 2 '11 at 17:
log in tour help Tour Start here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Database Administrators Questions Tags Users Badges Unanswered Ask Question _ Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute: Sign up Here's how it works: Anybody can ask a question Anybody can answer The best answers are voted up and rise to the top What are the causes and solutions for mutating table errors? up vote 7 down vote favorite 1 I understand mutating table errors are caused by a design flaw or problematic query. An old query was recently put into production which throws a mutating table error. Our DBA solved the problem but we do not know how. What exactly causes mutating table errors and how would our DBA have fixed the problem? sql oracle trigger plsql share|improve this question edited Jun 25 '15 at 17:56 Mahi_0707 1033 asked Sep 6 '11 at 8:46 parmanand 112238 add a comment| 3 Answers 3 active oldest votes up vote 13 down vote accepted The most likely cause of a mutating table error is the misuse of triggers. Here is a typical example: you insert a row in table A a trigger on table A (for each row) executes a query on table A, for example to compute a summary column Oracle throws an ORA-04091: table A is mutating, trigger/function may not see it This is an expected and normal behaviour, Oracle wants to protect you from yourself since Oracle guarantees: (i) that each statement is atomic (i.e will either fail or succeed completely) (ii) that each statement sees a consistent view of the data Most likely when you write this kind of trigger you would expect the query (2) to see the row inserted on (1). This would be in contradiction with both points above since the update is not finished yet (there could be more rows to be inserted). Oracle could return the result consistent with a point in time just before the beginning of the statement but from most of the examples I have seen that try to implement this logic, people see a multi-row statement as a serie of successive steps and expect the statement [2] to see the changes made by the previous ste