Ora-04091 Oracle Error
Contents |
MySQL MariaDB PostgreSQL SQLite MS Office Excel Access Word Web Development HTML CSS Color Picker Languages C Language More ASCII Table Linux UNIX Java Clipart Techie Humor Advertisement Oracle Basics ALIASES AND ora-04091 table is mutating trigger/function may not see it ora-06512 AND & OR BETWEEN COMPARISON OPERATORS DELETE DISTINCT EXISTS FROM GROUP BY HAVING ora-04091 solution IN INSERT INSERT ALL INTERSECT IS NOT NULL IS NULL JOIN LIKE MINUS NOT OR ORDER BY PIVOT REGEXP_LIKE SELECT mutating trigger in oracle 10g with example SUBQUERY TRUNCATE UNION UNION ALL UPDATE WHERE Oracle Advanced Oracle Cursors Oracle Exception Handling Oracle Foreign Keys Oracle Loops/Conditionals Oracle Transactions Oracle Triggers String/Char Functions Numeric/Math Functions Date/Time Functions Conversion Functions Analytic Functions oracle statement level trigger Advanced Functions Oracle / PLSQL: ORA-04091 Error Message Learn the cause and how to resolve the ORA-04091 error message in Oracle. Description When you encounter an ORA-04091 error, the following error message will appear: ORA-04091: table name is mutating, trigger/function may not see it Cause A statement executed a trigger or custom PL/SQL function. That trigger/function tried to modify or query a table that is
Mutating Trigger In Oracle 11g
currently being modified by the statement that fired the trigger/function. Resolution The option(s) to resolve this Oracle error are: Option #1 Re-write the trigger/function so that it does not try to modify/query the table in question. For example, if you've created a trigger against the table called orders and then the trigger performed a SELECT against the orders table as follows: CREATE OR REPLACE TRIGGER orders_after_insert AFTER INSERT ON orders FOR EACH ROW DECLARE v_quantity number; BEGIN SELECT quantity INTO v_quantity FROM orders WHERE order_id = 1; END; You would receive an error message as follows: When you create a trigger against a table, you can't modify/query that table until the trigger/function has completed. Remember that you can always use the :NEW and :OLD values within the trigger, depending on the type of trigger. Learn more about Triggers. Share this page: Advertisement Back to top Home | About Us | Contact Us | Testimonials | Donate While using this site, you agree to have read and accepted our Terms of Service and Privacy Policy. We use advertisements to support this website and fund the development of new content. Copyright © 2003-2016 TechOnTheNet.com. All rights reserved.
- 8:40 pm UTC Category: SQL*Plus � Version: 8.1.7 Whilst you are here, check out some content from the AskTom team: On the Explicit, Size, and Complex Latest Followup You Asked hello, i've got a table MRC and a trigger on it (AFTER INSERT) thus, after an
Oracle Instead Of Trigger
insert in the table MRC, this trigger has to determine if a new line must be ora-04091 after insert trigger inserted into an other table PLAN : for that, it does compare the :new values with the MOST RECENT enregistrement of MRC but i oracle mutating trigger pragma autonomous transaction got a mutating 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 https://www.techonthenet.com/oracle/errors/ora04091.php my logic. Have you considered the multi-user implications in your logic? Two people inserting at the same time (about the same time). What 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 https://asktom.oracle.com/pls/apex/f?p=100:11:0%3A%3A%3A%3AP11_QUESTION_ID:9579487119866 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 in general upwards (develop in 8.0 and goto 8.1) compatible but not backwards. Why I can't get the 4091 error when insert? January 05, 2004 - 3:39 am UTC Reviewer: Li ys from CHINA I only want to prove the mutating table by this triggers: CREATE TABLE r_Module ( Bureauno NUMBER(3), Moduleno NUMBER(3), primary key ( Bureauno, Moduleno ) ); CREATE OR REPLACE TRIGGER LimitTest BEFORE INSERT OR UPDATE ON r_Module FOR EACH ROW DECLARE v_MaxModuleNum CONSTANT NUMBER := 5; v_CurModuleNum NUMBER; BEGIN SELECT COUNT(*) INTO v_CurModuleNum FROM r_Module WHERE Bureauno = :new.Bureauno; IF v_CurModuleNum + 1 > v_MaxModuleNum THEN RAISE_APPLICATION_ERROR(-20000,'Too many Module in the Bureauno:'||:new.Bureauno); END IF; END; / When I insert some record into r_module table,I can'
here for a quick overview of the site Help Center Detailed answers to any questions you http://stackoverflow.com/questions/32716242/ora-04091-table-is-mutating-trigger-function-may-not-see-it-error-during-execu might have Meta Discuss the workings and policies of this site http://stackoverflow.com/questions/6915325/oracle-after-update-trigger-solving-ora-04091-mutating-table-error About Us Learn more about Stack Overflow the company 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 mutating trigger 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up ORA-04091: table is mutating, trigger/function may not see it error during execution of oracle trigger up vote 0 down vote favorite 1 I have below trigger in which for FIELD_NAME field i want to insert value mutating trigger in into FIELD_TRACKING table as 'Deactivation time of KPI in case of Downtime(Select KPI_FREQ_TIME_UNIT FROM KPI_DEFINITION)'. The bracket part in this string value comes from KPI_FREQ_TIME_UNIT field of KPI_DEFINITION table. So below is the trigger i have wrritten for this. The trigger compile without any error. But when i try to change the DNTM_REAC_AFTER_HRS field from the KPI_DEFINITION table then i am getting error ORA-04091: table RATOR_MONITORING_CONFIGURATION.KPI_DEFINITION is mutating, trigger/function may not see it ORA-04088: error during execution of trigger 'RATOR_MONITORING_CONFIGURATION.TRG_TRK_KPI_DEFINITION'. create or replace TRIGGER RATOR_MONITORING_CONFIGURATION."TRG_TRK_KPI_DEFINITION" AFTER UPDATE ON RATOR_MONITORING_CONFIGURATION.KPI_DEFINITION FOR EACH ROW IF NOT :old.DNTM_REAC_AFTER_HRS=:new.DNTM_REAC_AFTER_HRS THEN INSERT INTO RATOR_MONITORING_CONFIGURATION.FIELD_TRACKING (FIELD_TRACKING_ID,TABLE_NAME,TABLE_ID, FIELD_NAME,FIELD_OLD_VALUE,FIELD_NEW_VALUE,USER_ID, TIMESTAMP, FIELD_TRACKING_COMMENTS) VALUES (FIELD_TRACKING_SEQ.NEXTVAL,'KPI_DEFINITION',:new.KPI_DEF_ID,'Deactivation time of KPI in case of Downtime'|| '(' || to_char((Select KPI_FREQ_TIME_UNIT FROM KPI_DEFINITION)) || ')',to_char(:old.DNTM_REAC_AFTER_HRS),to_char( :new.DNTM_REAC_AFTER_HRS),:new.LAST_UPDATED_BY,:new.LAST_UPDATED_DATE, decode(:new.KPI_ACTIVE_DOWNTIME,'N','This KPI has been reactivated on end of a downtime.','')); END IF; END; oracle triggers sql-insert share|improve this question edited Sep 22 '15 at 12:02 asked Sep 22 '15 at 11:55 Andrew 5111624 add a comment| 2 Answers
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 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, 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: 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 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 a