Oracle Mutating Error Solution
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 Don Burleson Blog
Mutating Table Error In Oracle With Example
Fix Oracle mutating trigger table errors Oracle Database Tips by Burleson Consulting mutating trigger in oracle 11g A mutation table is defined as a table that is changing. But in dealing with triggers, it is a table that has
Mutating Trigger In Oracle 10g With Example
the possibility of changing. What this means to a trigger is that if the trigger reads a table, it can not change the table that it read from. This does not impact the exclusive use of oracle mutating trigger pragma autonomous transaction :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 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 mutating trigger with example 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. Commits, rollbacks and save points are not allowed in the trigger body. A commit/rollback affects the entire transaction, it is all or none. Unhandled exceptions in the trigger will cause a rollback of the entire transaction, not just the trigger. If more than one trigger is defined on an event, the order in which they fire is not defined. If the triggers must fire in order, you must create one trigger that executes all t
Load Balancing Mobile Devl Networking News Open Source Oracle Outsourcing Performance Tuning Protocols Security Sharepoint SQL Server Technology Unix Virtual Server Visual Studio VMWare Windows
Ora-04091 Solution
Windows Vista Questions? Please send your wish list of things that you oracle statement level trigger would like us to write about or if you have suggestions to help improve this blog site.
Oracle Instead Of Trigger
You can send all questions/suggestions to: Blog Support Archives Archives Select Month May 2016 (1) April 2016 (1) March 2016 (1) April 2013 (1) February 2013 (1) August http://www.dba-oracle.com/t_avoiding_mutating_table_error.htm 2012 (1) April 2012 (3) March 2012 (6) February 2012 (2) November 2011 (1) July 2011 (3) June 2011 (1) April 2011 (1) March 2011 (1) February 2011 (7) January 2011 (5) December 2010 (1) October 2010 (1) August 2010 (3) July 2010 (3) June 2010 (14) May 2010 (5) April 2010 (4) March 2010 (5) https://decipherinfosys.wordpress.com/2009/06/22/mutating-tabletrigger-error-and-how-to-resolve-it/ February 2010 (6) January 2010 (10) December 2009 (10) November 2009 (5) October 2009 (7) August 2009 (1) July 2009 (18) June 2009 (30) May 2009 (24) April 2009 (35) March 2009 (49) February 2009 (41) January 2009 (30) December 2008 (26) November 2008 (32) October 2008 (25) September 2008 (20) August 2008 (32) July 2008 (31) June 2008 (22) May 2008 (27) April 2008 (27) March 2008 (32) February 2008 (33) January 2008 (29) December 2007 (23) November 2007 (30) October 2007 (33) September 2007 (29) August 2007 (33) July 2007 (32) June 2007 (41) May 2007 (32) April 2007 (37) March 2007 (56) February 2007 (97) January 2007 (37) Blog Stats 6,865,469 Views Email Subscriptions Subscribe to Systems Engineering and RDBMS via Email RSS Subscriptions Subscribe to Systems Engineering and RDBMS via RSS Feed Recent Posts SQL Server 2016 - RTM on June1st Blockchain SSMS and .Net Framework4.5.1 SQL Server Virtualization Custom Search You can use our customized Search Engine based on Google's Co-op to nar
Social Links Printer Friendly About Search 8i | 9i | 10g | 11g | 12c | 13c | Misc | PL/SQL | SQL | RAC | WebLogic https://oracle-base.com/articles/9i/mutating-table-exceptions | Linux Home » Articles » 9i » Here Mutating Table Exceptions http://www.astral-consultancy.co.uk/cgi-bin/hunbug/doco.cgi?11010 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 methods to get round it. Test Schema Mutating Table Demonstration Solution 1 (Collection in mutating trigger 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 PRIMARY KEY (id) ); CREATE SEQUENCE tab1_seq; CREATE TABLE tab1_audit ( id NUMBER(10) NOT NULL, action VARCHAR2(10) NOT NULL, tab1_id mutating trigger in 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; / SHOW ERRORS Next we create the row-level trigger itself to catch any changes to the table. CREATE OR REPLACE TRIGGER tab1_ariu_trg AFTER INSERT OR UPDATE ON tab1 FOR EACH ROW BEGIN IF inserting THEN trigger_api.tab1_row_change(p_id => :new.id, p_action => 'INSERT');
Tuning Utility Scripts ORACLE FORMS Utility Scripts SERVER SCRIPT Perl Reference Perl Routines WINDOWS SCRIPT Data Manipulation XTRAS Www Tools SOCIAL BOOKMARKS Del.icio.us Digg This My Google Reddit Stumbleupon Mutating Tables: The Workaround A HunBug Document Document No.: 11010 Database Version: Oracle 9 / 10 Last Updated: 01 Mar 2008 Author: HunBug The mutating table error occurs when a trigger is trying to read a table that it is currently changing. It has to error as the data from reading the table could be misleading and depend on how the database processed the statement. This solution to the ORA-04091 mutating table error uses a package with triggers to workaround the issue. How to Recreate the Error Is this example a trigger is trying to query the table it is updating, so that it can update another. CREATE TABLE dept ( deptNo NUMBER(10,0) NOT NULL, deptName VARCHAR2(50) NOT NULL, empCount NUMBER(5,0) ); CREATE TABLE emp ( empNo NUMBER(10,0) NOT NULL, deptNo VARCHAR2(50) NOT NULL, empName VARCHAR2(50) NOT NULL ); CREATE OR REPLACE TRIGGER dtr_ar_updateEmpCount AFTER INSERT OR UPDATE OR DELETE ON emp FOR EACH ROW DECLARE PROCEDURE updateDept(p_deptNo IN NUMBER) IS BEGIN UPDATE dept SET empCount = ( SELECT COUNT(*) FROM emp WHERE deptNo = p_deptNo ) WHERE deptNo = p_deptNo; END updateDept; BEGIN IF INSERTING THEN updateDept(:NEW.deptNo); ELSIF UPDATING THEN updateDept(:OLD.deptNo); updateDept(:NEW.deptNo); ELSIF DELETING THEN updateDept(:OLD.deptNo); END IF; END; / The emp table is related to the dept table via the deptNo column. The dept.empCount column stores the count of employees for the department (Do not ask why, this is just an example) The trigger updates the dept.empCount column whenever the emp table data is changed. INSERT INTO dept ( deptNo, deptname, empCount ) VALUES ( 1,'Accounts',0 ); INSERT INTO dept ( deptNo, deptname, empCount ) VALUES ( 2,'Sales',0 );