Ora Error 4091
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 ora-04091 table is mutating trigger/function may not see it AND AND & OR BETWEEN COMPARISON OPERATORS DELETE DISTINCT EXISTS FROM GROUP ora-04091 solution BY HAVING IN INSERT INSERT ALL INTERSECT IS NOT NULL IS NULL JOIN LIKE MINUS NOT OR ORDER BY PIVOT
Mutating Trigger In Oracle 10g With Example
REGEXP_LIKE SELECT 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
Ora-04091 After Insert Trigger
Conversion Functions Analytic Functions 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 oracle statement level trigger query a table that is 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 devel
Topic Error messages Database Admin View All Cloud infrastructure Availability Backup and recovery Database design Export, import and migration Installation, upgrades and patches Oracle performance problems
Pragma Autonomous_transaction Trigger
and tuning Oracle security Oracle DBA tools MySQL database Real oracle instead of trigger Application Clusters (RAC) Applications View All Enterprise and business performance management Implementing and upgrading oracle mutating trigger pragma autonomous transaction Oracle apps E-Business Suite Fusion applications Hyperion JD Edwards (JDE) PeopleSoft Siebel and Oracle CRM Data Mgmt View All BI (business intelligence) Data quality https://www.techonthenet.com/oracle/errors/ora04091.php Data warehousing Metadata Database Admin View All Cloud infrastructure Availability Backup and recovery Database design Export, import and migration Installation, upgrades and patches Oracle performance problems and tuning Oracle security Oracle DBA tools Error messages MySQL database Real Application Clusters (RAC) Development View All BPEL in Oracle http://searchoracle.techtarget.com/answer/Workaround-for-the-ORA-4091-error-when-checking-ranges Open Source SQL Java / J2EE Stored procedures XML PL/SQL Fusion View All Oracle and BEA Application Server Data and application integration SOA (service-oriented architecture) Infrastructure View All Cloud computing infrastructure Exadata and Exalogic Grid computing Oracle on Linux Operating system Oracle hardware decisions Virtual machine Oracle management View All certification Oracle acquisitions Business process management Market analysis DBA jobs training and certification Small businesses Regulatory compliance Outsourcing Oracle on demand and SaaS Oracle strategy and product roadmap Oracle support services Oracle vs. SAP Oracle Web 2.0 Sun-Oracle infrastructure View All Oracle cloud computing Oracle OS Oracle virtualization Topics Archive View All Oracle DBA jobs Oracle Resources Training and certification Tutorials, tips and FAQs Please select a category Applications Data Mgmt Database Admin Development Fusion Infrastructure Oracle management Sun-Oracle infrastructure Section Problem Solve News Get Started Evaluate Manage Problem Solve Spon
ExpressionsReport Column PageResult SetSelect QuerySequenceSQL PlusStored Procedure FunctionSubquerySystem PackagesSystem Tables ViewsTableTable JoinsTriggerUser PreviliegeViewXMLThis trigger will raise an ORA-4091 mutating table error. : Trigger Error«Trigger«Oracle PL / SQLOracle PL / SQLTriggerTrigger http://www.java2s.com/Code/Oracle/Trigger/ThistriggerwillraiseanORA4091mutatingtableerror.htm ErrorThis trigger will raise an ORA-4091 mutating table error. SQL> SQL> CREATE TABLE lecturer ( 2 id NUMBER(5) PRIMARY KEY, 3 first_name VARCHAR2(20), 4 http://dba.stackexchange.com/questions/5432/what-are-the-causes-and-solutions-for-mutating-table-errors last_name VARCHAR2(20), 5 major VARCHAR2(30), 6 current_credits NUMBER(3) 7 ); Table created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits) 2 VALUES (10001, mutating trigger 'Scott', 'Lawson','Computer Science', 11); 1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits) 2 VALUES (10002, 'Mar', 'Wells','History', 4); 1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits) 2 VALUES (10003, 'Jone', 'Bliss','Computer Science', 8); 1 row created. SQL> SQL> INSERT ora error 4091 INTO lecturer (id, first_name, last_name, major,current_credits) 2 VALUES (10004, 'Man', 'Kyte','Economics', 8); 1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits) 2 VALUES (10005, 'Pat', 'Poll','History', 4); 1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits) 2 VALUES (10006, 'Tim', 'Viper','History', 4); 1 row created. SQL> SQL> SQL> CREATE OR REPLACE TRIGGER LimitMajors 2 BEFORE INSERT OR UPDATE OF major ON lecturer 3 FOR EACH ROW 4 DECLARE 5 studentMax CONSTANT NUMBER := 5; 6 studentCount NUMBER; 7 BEGIN 8 SELECT COUNT(*) 9 INTO studentCount 10 FROM lecturer 11 WHERE major = :new.major; 12 13 IF studentCount + 1 > studentMax THEN 14 RAISE_APPLICATION_ERROR(-20000, 15 'Too many lecturer in major ' || :new.major); 16 END IF; 17 END LimitMajors; 18 / Trigger created. SQL> SQL> UPDATE lecturer 2 SET major = 'History' 3 WHERE ID = 1
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 steps. Oracle can not return the expected result and therefore throws the