Oracle Error Codes Ora-04091
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 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
Mutating Trigger In Oracle 11g
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 oracle statement level trigger 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: You dont need that sequence number Latest Followup You Asked hello, i've got a table MRC and a trigger on it (AFTER INSERT) thus,
Ora-04091 After Insert Trigger
after an insert in the table MRC, this trigger has to determine if a new oracle instead of trigger line must be inserted into an other table PLAN : for that, it does compare the :new values with the MOST RECENT enregistrement oracle mutating trigger pragma autonomous transaction of MRC but i 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 https://www.techonthenet.com/oracle/errors/ora04091.php 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 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 https://asktom.oracle.com/pls/apex/f?p=100:11:0%3A%3A%3A%3AP11_QUESTION_ID:9579487119866 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 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.Bureau
Social Links Printer Friendly About Search 8i | 9i | 10g | 11g | 12c | 13c | Misc | https://oracle-base.com/articles/9i/mutating-table-exceptions PL/SQL | SQL | RAC | WebLogic | Linux Home » http://harmfultriggers.blogspot.com/2011/11/workarounds-for-ora-04091.html Articles » 9i » Here Mutating Table Exceptions Mutating table exceptions occur when we try to reference the triggering table in a query from within row-level trigger code. In this article I'll present examples of how a mutating table exception might occur and simple mutating trigger methods to get round it. Test Schema Mutating Table Demonstration Solution 1 (Collection in Package Variable) Solution 2 (Global Temporary Table) Test Schema The following schema objects are necessary to run the code in this article. CREATE TABLE tab1 ( id NUMBER(10) NOT NULL, description VARCHAR2(50) NOT NULL ); ALTER TABLE tab1 ADD ( CONSTRAINT tab1_pk mutating trigger in PRIMARY KEY (id) ); CREATE SEQUENCE tab1_seq; CREATE TABLE tab1_audit ( id NUMBER(10) NOT NULL, action VARCHAR2(10) NOT NULL, tab1_id NUMBER(10), record_count NUMBER(10), created_time TIMESTAMP ); ALTER TABLE tab1_audit ADD ( CONSTRAINT tab1_audit_pk PRIMARY KEY (id) ); ALTER TABLE tab1_audit ADD ( CONSTRAINT tab1_audit_tab1_fk FOREIGN KEY (tab1_id) REFERENCES tab1(id) ); CREATE SEQUENCE tab1_audit_seq; Mutating Table Demonstration Let's assume we need to audit the actions on the parent table and for some reason, this involves querying the triggering table. We can demonstrate this with the following package and trigger. We place all our trigger code into a package as follows. CREATE OR REPLACE PACKAGE trigger_api AS PROCEDURE tab1_row_change (p_id IN tab1.id%TYPE, p_action IN VARCHAR2); END trigger_api; / SHOW ERRORS CREATE OR REPLACE PACKAGE BODY trigger_api AS PROCEDURE tab1_row_change (p_id IN tab1.id%TYPE, p_action IN VARCHAR2) IS l_count NUMBER(10) := 0; BEGIN SELECT COUNT(*) INTO l_count FROM tab1; INSERT INTO tab1_audit (id, action, tab1_id, record_count, created_time) VALUES (tab1_audit_seq.NEXTVAL, p_action, p_id, l_count, SYSTIMESTAMP); END tab1_row_change; END trigger_api;
ORA-04091 In the previous post, we demonstrated running into the mutating table error. The example involved the well-known EMP table and a business rule that we are trying to implement: all departments with a Manager should also employ a Clerk. We started our implementation for that rule by writing a straight-forward 'after-row-insert' trigger on the EMP table. The row trigger detects whenever a Manager is inserted (remember, we can only detect this with a row trigger, not with a statement trigger); It then calls a stored procedure to which it supplies the department number (deptno-column value) of the inserted Manager; The procedure then queries the EMP table to see if a Clerk exists in this department; If it finds no such Clerk, the procedure raises an application error, which causes the after-insert-row trigger to fail. This in turn prevents the insert of the Manager, since it would violate our business rule (the Manager insert will undergo a statement-level rollback due to the row-trigger failing); If it finds such a Clerk, then the procedure finishessuccessfully, which causes the after-insert-row trigger to finish successfully, which causes the Manager insert to execute successfully. A straight-forward 'use-case' of using a (row) trigger, right? Of course this setup didn't work, since the cursor in the stored procedure will always hit the mutating table error, thus preventing a Manager insert even if the necessary Clerk would indeed be present. So, what's the workaround for this? Well there are a two I would like to discuss in this post: one of them is very 'popular', the other one is -- I guess -- not so well-known. I cannot stress enough upfront right now, that both 'workarounds' are red herrings. Workaround 1a: use the autonomous_transaction pragma inside the stored procedure. If we add just one line at the top of the stored procedure, we can fix this baby (see red-arrow line below). By ensuring that all code inside the P_CHECK_FOR_CLERK procedure will run as an autonomous transaction, we can prevent the mutating table error. Why? Because it causes Oracle to 'step out' of the current transaction context, in which EMP is currently 'mutating', and open a new transaction in which EMP is not mutating, and therefor can be queried without hitting the mutating table error. So all we need to do i