Ora 04091 Error In Oracle
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
ora-04091 table is mutating Fix Oracle mutating trigger table errors Oracle Database Tips by Burleson Consulting A mutation tableOra-04091 Solution
is defined as a table that is changing. But in dealing with triggers, it is a table that has the possibility of
Mutating Trigger In Oracle 10g With Example
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
Oracle Statement Level Trigger
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 with Oracle9i and above. If a trigger mutating trigger in oracle 11g 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 the actions in the required order. A trigger can cause other events to execute triggers.
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 oracle instead of trigger Learn more about Stack Overflow the company Business Learn more about hiring developers or ora-04091 after insert trigger posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow oracle mutating trigger pragma autonomous transaction 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 ORACLE After update trigger: solving ORA-04091 mutating table http://www.dba-oracle.com/t_avoiding_mutating_table_error.htm error up vote 2 down vote favorite I am trying to create a trigger: create or replace trigger NAME_OF_TRIGGER after insert or update on table1 REFERENCING OLD AS OLD NEW AS NEW for each row to fill in automatically a couple of non obligatory fields when updating/inserting on a table. This requires me to use a cursor that selects from table2 and also table1 (the subject of the http://stackoverflow.com/questions/6915325/oracle-after-update-trigger-solving-ora-04091-mutating-table-error trigger). Is there any way to avoid the mutating table error without using a temporary table for values or an autonomous transaction? sql oracle plsql triggers mutating-table share|improve this question edited Aug 2 '11 at 16:54 APC 87.3k1384184 asked Aug 2 '11 at 16:26 JoséNunoFerreira 150111 add a comment| 1 Answer 1 active oldest votes up vote 7 down vote accepted "Is there any way to avoid the mutating table error without using a temporary table for values or an autonomous transaction?" tl;dr no. The mutating table error is caused by querying the table which owns the trigger, or tables which are involved in a foreign key relationship with the owning table (at least in older versions of the database, not sure whether it still obtains). In a properly designed application this should not be necessary. This is why many people regard mutating tables as an indicator of poor data modelling. For instance, mutation is often associated with insufficient normalisation. To paraphrase Jamie Zawinski: Some people, when confronted with a mutating table exception, think "I know, I'll use autonomous transactions." Now they have two problems. Sometimes the error can be avoided by simply modifying the :NEW values in a
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 http://stackoverflow.com/questions/10500663/oracle-triggers-error-ora-01427-error-ora-04091 site About Us Learn more about Stack Overflow the company Business Learn more http://stackoverflow.com/questions/32716242/ora-04091-table-is-mutating-trigger-function-may-not-see-it-error-during-execu 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 oracle triggers in oracle error ORA-01427 & error ORA-04091 up vote 0 down vote favorite I have the following tables: FACULTY table CREATE TABLE "FACULTY" ( "FACULTY_ID" NUMBER(3,0), "FACULTY_NAME" VARCHAR2(30), "FACULTY_DEAN" VARCHAR2(30), CONSTRAINT "FACULTY_PK" PRIMARY KEY ("FACULTY_ID") ENABLE ) COURSE table CREATE TABLE "COURSE" ( "COURSE_ID" NUMBER(5,0), "COURSE_NAME" VARCHAR2(50), "COURSE_LEVEL" NUMBER, "FACULTY" NUMBER, CONSTRAINT "COURSE_PK" PRIMARY KEY ("COURSE_ID") ENABLE ) so now i want to achieve two things mutating trigger in (1) when a faculty_id is updated on the faculty table. a trigger will fire and update the corresponding rows in the course table with new faculty_id. it will also store the old faculty_id value, name of course, and the date in which the operation is performed in a course_log table. Below is what I got create or replace trigger update_faculty after update on faculty for each row begin insert into course_log values (:old.faculty_id, (select course_name from course where faculty=:old.faculty_id), sysdate); update course set faculty=:new.faculty_id where faculty=:old.faculty_id; end; But I get the following error. error ORA-01427: single-row subquery returns more than one row ORA-06512: at "SYSTEM.UPDATE_FACULTY", line 2 ORA-04088: error during execution of trigger 'SYSTEM.UPDATE_FACULTY' any ideas on how to solve it? (2) Write a trigger that fires when try change the course_id attribute in the course table, which will check whether the value already exists in the course table and will update successfully if it is a new value. If the value already exists in any row, the trigger will throw an application error saying "The course_id already exists! Update not successful." below is my query CREATE OR REPLACE TRIGGER "UPD
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 ORA-04091: table is mutating, trigger/function may not see it error during execution of oracle trigger up vote 0 down vote favorite 1 I have below trigger in which for FIELD_NAME field i want to insert value into FIELD_TRACKING table as 'Deactivation time of KPI in case of Downtime(Select KPI_FREQ_TIME_UNIT FROM KPI_DEFINITION)'. The bracket part in this string value comes from KPI_FREQ_TIME_UNIT field of KPI_DEFINITION table. So below is the trigger i have wrritten for this. The trigger compile without any error. But when i try to change the DNTM_REAC_AFTER_HRS field from the KPI_DEFINITION table then i am getting error ORA-04091: table RATOR_MONITORING_CONFIGURATION.KPI_DEFINITION is mutating, trigger/function may not see it ORA-04088: error during execution of trigger 'RATOR_MONITORING_CONFIGURATION.TRG_TRK_KPI_DEFINITION'. create or replace TRIGGER RATOR_MONITORING_CONFIGURATION."TRG_TRK_KPI_DEFINITION" AFTER UPDATE ON RATOR_MONITORING_CONFIGURATION.KPI_DEFINITION FOR EACH ROW IF NOT :old.DNTM_REAC_AFTER_HRS=:new.DNTM_REAC_AFTER_HRS THEN INSERT INTO RATOR_MONITORING_CONFIGURATION.FIELD_TRACKING (FIELD_TRACKING_ID,TABLE_NAME,TABLE_ID, FIELD_NAME,FIELD_OLD_VALUE,FIELD_NEW_VALUE,USER_ID, TIMESTAMP, FIELD_TRACKING_COMMENTS) VALUES (FIELD_TRACKING_SEQ.NEXTVAL,'KPI_DEFINITION',:new.KPI_DEF_ID,'Deactivation time of KPI in case of Downtime'|| '(' || to_char((Select KPI_FREQ_TIME_UNIT FROM KPI_DEFINITION)) || ')',to_char(:old.DNTM_REAC_AFTER_HRS),to_char( :new.DNTM_REAC_AFTER_HRS),:new.LAST_UPDATED_BY,:new.LAST_UPDATED_DATE, decode(:new.KPI_ACTIVE_DOWNTIME,'N','This KPI has been reactivated on end of a downtime.','')); END IF;