Oracle 4091 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 mutating trigger in oracle 10g with example AND & OR BETWEEN COMPARISON OPERATORS DELETE DISTINCT EXISTS FROM GROUP BY HAVING
Mutating Table Error In Oracle With Example
IN INSERT INSERT ALL INTERSECT IS NOT NULL IS NULL JOIN LIKE MINUS NOT OR ORDER BY PIVOT REGEXP_LIKE SELECT
Oracle Statement Level Trigger
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
Ora-04091 Solution
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.
RESOURCES Database Tools SQL Scripts & Samples Links » Database Forum » Slideshows » Sitemap Free Newsletters: DatabaseDaily News Via RSS Feed Database Journal |DBA oracle instead of trigger Support |SQLCourse |SQLCourse2 Featured Database Articles Oracle Posted Mar 23, 2004 oracle mutating trigger pragma autonomous transaction The Mutation error in Oracle Database Triggers By Amar Kumar Padhi The Mutating table error is a well-known problem oracle after trigger encountered in development; most developers have come across this error. ORA-04091: table
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 ErrorThis trigger http://www.java2s.com/Code/Oracle/Trigger/ThistriggerwillraiseanORA4091mutatingtableerror.htm 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 last_name VARCHAR2(20), 5 major http://mundodb.es/evitar-el-error-oracle-ora-04091-tabla-mutante 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, 'Scott', 'Lawson','Computer Science', 11); 1 mutating trigger 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 INTO lecturer (id, first_name, last_name, major,current_credits) 2 mutating trigger in 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 = 10003; UPDATE lecturer * ERROR at line 1: ORA-04091: table JAVA2S.LECTURER is mutating, trigger/function
el error Oracle ORA-04091 (tabla mutante) 12 diciembre, 2013 Publicado por Jesús García Comentarios desactivados en Evitar el error Oracle ORA-04091 (tabla mutante) trucos SQL Este error ocurre usualmente al realizar una consulta de un campo desde un trigger y ese campo es el que hace, que salte el trigger. Vamos a ver un ejemplo, que produce este error: -- Creamos una tabla CREATE TABLE usuarios (id Number, nombre VARCHAR2(10)); -- Insertamos registros de prueba INSERT INTO usuarios values (1, 'Pepe'); INSERT INTO usuarios values (2, 'Juan'); INSERT INTO usuarios values (3, 'Luis'); COMMIT; -- Creamos un trigger CREATE OR REPLACE TRIGGER TAU_usuarios AFTER UPDATE OF nombre ON usuarios FOR EACH ROW DECLARE v_Contador NUMBER; BEGIN SELECT count(*) INTO v_Contador FROM usuarios WHERE nombre = 'Luis'; DBMS_OUTPUT.PUT_LINE(‘El número de Luises son: ‘ || v_Contador); END; Y ahora actualizamos el campo que es consultado en el trigger: UPDATE usuarios SET nombre = 'Luis' WHERE id = 1; Y nos salta el error de tabla mutante: ERROR en línea 1: ORA-04091: la tabla usuarios está mutando, puede que el disparador/la función no puedan verla ORA-06512: en "USER.TAU_USUARIOS", línea 5 ORA-04088: error durante la ejecución del disparador 'USER.TAU_USUARIOS' SOLUCIONES A LOS ERRORES TABLA MUTANTE (ORA-04091) SOLUCIÓN 1: EVITAR TRATAMIENTO A NIVEL DE FILA En vez de tratar a nivel de registro, se pudiera tratar a nivel de sentencia evitando poner "FOR EACH ROW", se solventaría el problema. Claro está, siempre que lo que se pretenda con el trigger este cubierto con esta menor granularidad. Quedaría de la siguiente manera: CREATE OR REPLACE TRIGGER TAU_usuarios AFTER UPDATE OF nombre ON usuarios DECLARE v_Contador NUMBER; BEGIN SELECT count(*) INTO v_Contador FROM usuarios WHERE nombre = 'Luis'; DBMS_OUTPUT.PUT_LINE(‘El número de Luises son: ‘ || v_Contador); END; Actualizamos otra vez: UPDATE usuarios SET nombre = 'Luis' WHERE id = 1; El número de Luises son: 2 1 fila actualizada. Como vemos finaliza correctamente. Ha finalizado además indicando correctamente el número de Luises totales a 2, que son los que hay al final en la tabla. SOLUCIÓN 2: TRANSACCIONES AUTÓNOMAS Las transacciones