Oracle Table Is Mutating Error
Contents |
log in tour help Tour Start 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 mutating trigger in oracle 11g posting ads with us Database Administrators Questions Tags Users Badges Unanswered Ask Question _ Database Administrators mutating trigger in oracle 10g with example Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. mutating trigger with example Join them; it only takes a minute: Sign up Here's how it works: Anybody can ask a question Anybody can answer The best answers are voted up and rise to the top What are the causes and solutions for mutating table
Oracle Mutating Trigger Pragma Autonomous Transaction
errors? up vote 7 down vote favorite 1 I understand mutating table errors are caused by a design flaw or problematic query. An old query was recently put into production which throws a mutating table error. Our DBA solved the problem but we do not know how. What exactly causes mutating table errors and how would our DBA have fixed the problem? sql oracle trigger plsql share|improve this question edited Jun 25 '15 at 17:56 Mahi_0707 1033 asked Sep 6 '11 at oracle statement level trigger 8:46 parmanand 112238 add a comment| 3 Answers 3 active oldest votes up vote 13 down vote accepted The most likely cause of a mutating table error is the misuse of triggers. Here is a typical example: you insert a row in table A a trigger on table A (for each row) executes a query on table A, for example to compute a summary column Oracle throws an ORA-04091: table A is mutating, trigger/function may not see it This is an expected and normal behaviour, Oracle wants to protect you from yourself since Oracle guarantees: (i) that each statement is atomic (i.e will either fail or succeed completely) (ii) that each statement sees a consistent view of the data Most likely when you write this kind of trigger you would expect the query (2) to see the row inserted on (1). This would be in contradiction with both points above since the update is not finished yet (there could be more rows to be inserted). Oracle could return the result consistent with a point in time just before the beginning of the statement but from most of the examples I have seen that try to implement this logic, people see a multi-row statement as a serie of successive steps and expect the statement [2] to see the changes made by the previous steps. Oracle can not return the expected result and therefore throws the error. For further reading: "mutating table" on Ask Tom. I
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 ora-04091 solution Burleson Blog
Oracle Instead Of Trigger
Fix Oracle mutating trigger table errors Oracle Database Tips by Burleson Consulting A mutation table is
Mutating Table Error In Oracle 11g With Example
defined 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 if http://dba.stackexchange.com/questions/5432/what-are-the-causes-and-solutions-for-mutating-table-errors 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 insert http://www.dba-oracle.com/t_avoiding_mutating_table_error.htm 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. Commits, rollbacks and save points
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 http://www.akadia.com/services/ora_mutating_table_problems.html Guide. A mutating table is a table that is currently being modified http://stackoverflow.com/questions/16182089/table-is-mutating-trigger-function-may-not-see-it-stopping-an-average-grade-fr 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. mutating trigger 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 trigger in 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; E
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 6.2 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