Avoiding Mutating Table 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
How To Avoid Mutating Error In Oracle Trigger
Don Burleson Blog
mutating table error in oracle 11g Fix Oracle mutating trigger table errors Oracle tips by Burleson A mutation table is defined mutating table error in oracle with example 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 ifMutating Trigger In Oracle 10g With Example
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
Oracle Compound Trigger
insert 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. Co
- 8:40 pm UTC Category: SQL*Plus – Version: 8.1.7 Latest Followup You Asked hello, i've got a table MRC and a trigger on it (AFTER INSERT) thus, after an insert in the table MRC, this trigger has to determine if a new line must mutating trigger in oracle 11g be inserted into an other table PLAN : for that, it does compare the :new
Mutating Trigger With Example
values with the MOST RECENT enregistrement of MRC but i got a mutating table error i understand the problem but how can i oracle mutating trigger pragma autonomous transaction 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 http://www.dba-oracle.com/t_avoiding_mutating_table_error.htm 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 if you persist, you can use the technique: https://asktom.oracle.com/pls/apex/f?p=100:11:0%3A%3A%3A%3AP11_QUESTION_ID:9579487119866 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
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 and tuning Oracle security Oracle DBA tools MySQL database Real http://searchoracle.techtarget.com/tip/How-to-avoid-mutating-table-errors Application Clusters (RAC) Applications View All Enterprise and business performance management Implementing and upgrading Oracle apps E-Business Suite Fusion applications Hyperion JD Edwards (JDE) PeopleSoft Siebel and Oracle CRM http://www.akadia.com/services/ora_mutating_table_problems.html Data Mgmt View All BI (business intelligence) Data quality Data warehousing Metadata Database Admin View All Cloud infrastructure Availability Backup and recovery Database design Export, import and migration in oracle 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 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 mutating table error 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 Manage News Get Started Evaluate Manage Problem Solve Sponsored Communities How to avoid 'mutating table' errors byPravesh Gupta A "mutating table" error can be avoided by writing a procedure/function that declares the pragma autonomous_transaction. In this Article Share this item with your network: Related Content Trigger causing table to mutate – SearchOracle PL/SQL program for complex 8i output – SearchOracle Performing a NOT IN without a subquery – SearchOracl
4 ORA-04088: error during execution of trigger 'TRG_GET_ID_APP'. We studied the "Mutating Table" Problem and found the following explanations in the Oracle8i Application Developer's Guide. A mutating table is a table that is currently being modified by an UPDATE, DELETE, or INSERT statement, or it is a table that might need to be updated by the effects of a declarative DELETE CASCADE referential integrity constraint. The restrictions on such a table apply only to the session that issued the statement in progress. For all row triggers, that were fired as the result of a DELETE CASCADE, there are two important restrictions regarding mutating tables. These restrictions prevent a trigger from seeing an inconsistent set of data. The SQL statements of a trigger cannot read from (query) or modify a mutating table of the triggering statement. Example 1 Select in a mutating table from a row trigger (Tested on Oracle 8.1.7) We want to explain this situation on an example. We have two tables "A" and "B". "A" is the master table and "B" the detail table. We specified a foreign key between "B" and "A" with the CASCADE DELETE option. Here are the CREATE statements drop table B; drop table A; create table A ( ida number not null, vala varchar2(10), primary key(ida)); create table B ( idb number, valb varchar2(10), foreign key (idb) references A (ida) on delete cascade) / create or replace trigger b_br after delete on B for each row declare n integer; begin select count(*) into n from A; dbms_output.put_line('there are ' || n || ' rows in A'); dbms_output.put_line('after statment on B'); dbms_output.new_line; end; / insert into A values(1,'Table A'); insert into A values(2,'Table A'); insert into B values(1,'Table B'); insert into B values(1,'Table B'); commit; set serveroutput on; delete from A where idA = 1; ERROR at line 1: ORA-04091: table SCOTT.A is mutating, trigger/function may not see ORA-06512: at "SCOTT.B_BR", line 4 ORA-04088: error during execution of trigger 'SCOTT.B_BR' Notice that the SQL statement ( "select count(*) into n from A" ) is run for the first row of the table, and then the AFTER row trigger B_BR is fired. In turn, a statement in the AFTER row trigger body attempts to query the original table A. However, because the table A is mutating due to the CASCADE DELETE foreign key, this query is not allowed by Oracle. If attempted, a runtime error occurs, the effects of the trigger body and triggering statement are rolled back, and control is return