Oracle Table Mutation Error
Contents |
Social Links Printer Friendly About Search 8i | 9i | 10g | 11g | 12c | 13c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux Home mutating trigger in oracle 11g » Articles » 9i » Here Mutating Table Exceptions Mutating table exceptions occur mutating trigger in oracle 10g with example when we try to reference the triggering table in a query from within row-level trigger code. In this article I'll
Mutating Trigger With Example
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 Package Variable) Solution 2 (Global Temporary Table)
Oracle Mutating Trigger Pragma Autonomous Transaction
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 NUMBER(10), record_count NUMBER(10), created_time TIMESTAMP ); ALTER TABLE tab1_audit ADD oracle statement level trigger ( 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'); ELSE trigger_api.tab1_row_change(p_id => :new.id, p_action => 'UPDATE'); END IF; END; / SHOW ERRORS If we try to insert into the TA
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
Ora-04091 Solution
Fix oracle instead of trigger Oracle mutating trigger table errors Oracle Database Tips by Burleson Consulting A mutation table is defined as a table that mutating table error in oracle 11g with example 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 if the trigger reads a table, it https://oracle-base.com/articles/9i/mutating-table-exceptions 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 insert on a child table referencing a foreign key. Mutating http://www.dba-oracle.com/t_avoiding_mutating_table_error.htm 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. Commits, rollbacks and save points are not allowed in the trigger body. A commit/rollback affects the entire transaction, it is all or n
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 http://www.akadia.com/services/ora_mutating_table_problems.html 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 mutating trigger 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 mutating trigger in 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