How To Handle 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 Don Burleson Blog
mutating trigger in oracle 11g Fix Oracle mutating trigger table errors Oracle Database Tips by Burleson Consulting mutating trigger in oracle 10g with example A mutation table is defined as a table that is changing. But in dealing with triggers, it is a table that has mutating trigger 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 problemStatement Level Trigger In Oracle
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 th
Social Links Printer Friendly About Search 8i | 9i | 10g | 11g | 12c | 13c | Misc | PL/SQL | SQL |
Pragma Autonomous_transaction In Trigger
RAC | WebLogic | Linux Home » Articles » 9i » mutating table error in oracle 11g with example Here Mutating Table Exceptions Mutating table exceptions occur when we try to reference the triggering table in a ora-04091 solution 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 http://www.dba-oracle.com/t_avoiding_mutating_table_error.htm 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 PRIMARY KEY (id) ); CREATE SEQUENCE tab1_seq; CREATE TABLE tab1_audit https://oracle-base.com/articles/9i/mutating-table-exceptions ( 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; / SHOW ERRORS Next we create the row-level trigger itself to catch any changes to the table. CREATE OR REPLACE TRIGGE
- 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 https://asktom.oracle.com/pls/apex/f?p=100:11:0%3A%3A%3A%3AP11_QUESTION_ID:9579487119866 if a new line must be inserted into an other table PLAN : for that, it does compare the :new values with the MOST RECENT enregistrement of MRC but i got a mutating table error i http://stackoverflow.com/questions/16182089/table-is-mutating-trigger-function-may-not-see-it-stopping-an-average-grade-fr 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 serious fatal flaw in my logic. mutating trigger 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 trigger in oracle 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_M
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Table is mutating, trigger/function may not see it (stopping an average grade from dropping below 2.5) up vote 1 down vote favorite Here's the problem: Create a trigger that prevents any change to the taking relation that would drop the overall average grade in any particular class below 2.5. Note: This trigger is not intended to address the average GPA of any given student, but rather it should address the average grade for all grades assigned in a particular class. Here's the schema: Student-schema =(studentnum, name, standing, gpa, major) Class-schema = (schedulenum, semester, department, classnum, days, time, place, enrollment) Instructor-schema = (name, department, office) Teaches-schema = (name, schedulenum, semester) Taking-schema = (studentnum, schedulenum, semester, grade) I'm having a terrible time with these triggers, but here's my attempt to make this work: CREATE OR REPLACE TRIGGER stopChange AFTER UPDATE OR INSERT OR DELETE ON taking REFERENCING OLD AS old NEW AS new FOR EACH ROW DECLARE grd_avg taking.grade%TYPE; BEGIN SELECT AVG(grade) INTO grd_avg FROM taking WHERE studentnum = :new.studentnum AND schedulenum = :new.schedulenum AND semester = :new.semester; IF grd_avg < 2.5 THEN UPDATE taking SET grade = :old.grade WHERE studentnum = :old.studentnum AND schedulenum = :old.schedulenum AND semester = :old.semester; END IF; END; / I'm obviously doing something wrong because when I then go to update or delete a tuple, I get the error: ERROR at line 1: ORA-04091: table TAKING is mutating, trigger/function may not see it ORA-06512: at "STOPCHANGE", line 6 ORA-04088: error during execution of trigger 'STOPCHANGE' Any advice? I'm using Oracle. sql oracle triggers share|improve this question asked Apr 24 '13 at 1:51 The Rationalist 3231513 add a comment| 5 Answers 5 active oldest votes up vote 0 down vote accepted I think you can fix this by rewriting this as a before trigger, rather than an after trigger. However, this might be a little complicated for inserts and deletes. The idea is: CREATE OR REPLACE TRIGGER stopChange BEFORE UPDATE OR INSERT OR DELETE