Oracle Error Ora 04091
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 ora-04091 solution Books Oracle Scripts Ion Excel-DB Don Burleson Blog mutating trigger in oracle 10g with example
Fix Oracle mutating trigger table errorsMutating Trigger In Oracle 11g
Oracle Database Tips by Burleson Consulting A mutation table is defined as a table that is changing. But in dealing with triggers, it is a table that has
Oracle Statement Level Trigger
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 :OLD and :NEW. It says that if the trigger reads the table (such as using a SELECT query), that oracle instead of trigger 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 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 tr
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
Oracle Mutating Trigger Pragma Autonomous Transaction
Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs oracle after trigger Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just pragma autonomous_transaction trigger 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 http://www.dba-oracle.com/t_avoiding_mutating_table_error.htm 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, http://stackoverflow.com/questions/16182089/table-is-mutating-trigger-function-may-not-see-it-stopping-an-average-grade-fr 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 TRI
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 http://stackoverflow.com/questions/19150671/ora-04091-table-is-mutating 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 ora-04091 table is Mutating- up vote 0 down vote favorite I'm using a function which compares all the columns in Table 1 and mutating trigger Table 2 and returns 'Y' or 'N'. Based on that, I will update my Table 1. But when I run the merge statement it displays an error: ora-04091 - Table1 is mutating, trigger/function may not see it How can I fix this? CREATE OR REPLACE function DataChange (in_epmname varchar2) return char is v_epmname table2.empname%type; v_DATA_COUNT varchar2(2); v_DATA_CHANGED char; begin SELECT COUNT (*) into v_DATA_COUNT FROM ( SELECT trim(column1||column2||column3) FROM table1 WHERE empname = in_epmname mutating trigger in UNION SELECT trim(column1||column2||column3) FROM table2 WHERE empname = in_epmname ); If (v_DATA_COUNT = '1' ) Then v_DATA_CHANGED :='N'; else v_DATA_CHANGED :='Y'; end if; return v_DATA_CHANGED; end DataChange ; The merge statement that I'm using is: CREATE OR REPLACE PROCEDURE updatetabble1 AS BEGIN MERGE INTO Table1 DBC USING ( SELECT empname, DataChange(empname) as DATA_CHANGED FROM employee ) TBL_MAIN ON ( DBC.empname = TBL_MAIN.empname ) WHEN MATCHED THEN UPDATE SET DBC.DATA_CHANGED = TBL_MAIN.DATA_CHANGED; COMMIT; END updatetabble1; oracle ora-04091 share|improve this question edited Oct 3 '13 at 4:28 Mat 136k21235274 asked Oct 3 '13 at 4:18 Ragav 13212 Not sure I understand exactly what you're trying to achieve, but wouldn't you be better served with a trigger on update of table 2 to set that flag in table 1? –Mat Oct 3 '13 at 4:32 possible duplicate of Mutating Table in Oracle 11 caused by a function –A.B.Cade Oct 3 '13 at 4:34 Table1 is a kill and fill table and it has 50+ columns which are derived from other tables. MoreOver this DATA_CHANGED column in Table1 if found as 'Y' then i will update my Table2 with latest records which i received in 50+ columns in Table1 –Ragav Oct 3 '13 at 4:37 thanks Cade, is there anyother way to implement this using the same Func