How To Overcome Mutating 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
Fix mutating trigger in oracle 11g Oracle mutating trigger table errors Oracle Database Tips by Burleson Consulting A mutation table is mutating trigger in oracle 10g 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 mutating trigger with example 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 oracle mutating trigger pragma autonomous transaction 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 does result inMutating Table Error In Oracle 11g With Example
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. A trigger can not change a
Management Accounting Operating System Operating System Android iOS Microsoft Coding Programming J2EE Languages Database Database Data Warehousing Oracle Applications Applications Clarify CRM Oracle Apps Peoplesoft Siebel Software
Ora-04091 Solution
Testing Testing Testing Tools Miscelleanous Helpdesk Mainframe Networking SAP R/3 Web Interview statement level trigger in oracle Coaching Career Counseling Job Interview Interview eBooks Accounting eBook C eBook Citrix eBook Job Interview eBook Career Advice pragma autonomous_transaction in trigger Career Change Jobs Help Projects Requests Resume Help Training Advice Engineering Chemical Engineering Civil Engineering Electrical Engineering Electronics Engineering Micro Processor Mechanical Engineering By Company Ask Question Home Interview QuestionsOraclePL/SQL http://www.dba-oracle.com/t_avoiding_mutating_table_error.htm What is mutating trigger? How do you resolve it?If a trigger body is trying to update dependent tables, will it get a mutating error? Interview Candidate Dec 1st, 2006 16 52611 PL/SQL Answer First Prev Next Last Showing Answers 1 - 16 of 16 Answers PAWAN AHUJA Dec 3rd, 2006 Mutating trigger is trigger that is currently being modified http://www.geekinterview.com/question_details/38947 by DML opertion.we can resolve it by applying statement lelel trigger.RegardsPawan Ahuja
Was this answer useful?Yes 1 Reply manikandan Dec 8th, 2006 if the trigger attempts to select or modify the table while the trigger has not completed (ie. table is in transition). then mutating trigger error occurs.because the table is in middle of a transaction so it causes the trigger to mutate.you can change the trigger to statement level and apply the logic there. Was this answer useful?Yes Reply vikramssheth ProfileAnswers by vikramssheth Jan 30th, 2007 We can override this problem using temporary Table Concept. Was this answer useful?Yes Reply sripri ProfileAnswers by sripriQuestions by sripri May 8th, 2007 Mutating trigger has similar concept to that of deadlock. When a table is in the half way of executing a transaction and is the owner of a trigger and if the same table is accessed by the trigger somewhere else in the same time then at that instance mutating trigger occurs. Was this answer useful?Yes Reply Robert ProfileAnswers by RobertQuestions by Robert May 8th, 200CommunityOracle User Group CommunityTopliners CommunityOTN Speaker BureauJava CommunityError: You don't have JavaScript enabled. This tool uses JavaScript and much of it will not work correctly without it enabled. Please turn JavaScript back on and reload this page. Please enter a title. You can not post a blank message. Please type your message and try again. https://community.oracle.com/thread/476329 More discussions in PL/SQL and SQL All PlacesDatabaseDatabase Application DevelopmentPL/SQL and SQL This discussion is archived 1 2 Previous Next 19 Replies Latest reply on Jul 24, 2008 2:27 PM by BluShadow how to overcome mutating table error. 544866 Feb 12, 2007 10:59 AM Hi, I am writing a trigger on a table.In that trigger i want to query the same table for checking the row which i am going to insert exists or not by takeing mutating trigger rowcount. What is the solution for this? Thanks in advance, Nilesh Malekar 13708Views Tags: none (add) This content has been marked as final. Show 19 replies 1. Re: how to overcome mutating table error. Satish Kandi Feb 12, 2007 11:09 AM (in response to 544866) Use packages. Check out this link Like Show 0 Likes(0) Actions 2. Re: how to overcome mutating table error. 460631 Feb 12, 2007 11:18 AM (in response to 544866) u can even trigger in oracle use Autonomus trigger to avoid Mutating table error. U just have to add Pragma Autonomous_transaction in declare & include a commit. Although this practice has its own drawbacks as the trigger will be independent from the triggering event .Hence it may cause discrepency of data Like Show 0 Likes(0) Actions 3. Re: how to overcome mutating table error. Colin'tHart Feb 12, 2007 11:58 AM (in response to 544866) Why can't you use a unique constraint? Cheers, Colin Like Show 0 Likes(0) Actions 4. Re: how to overcome mutating table error. APC Feb 12, 2007 12:17 PM (in response to Colin'tHart) Why can't you use a unique constraint?Alternatively, depending on what action you want to happen if the row does exist, MERGE? Mutating table errors are almost always an indicator of a sub-optimal design, so it is worth checking our requirements and assumptions to see if there is a different approach which will meet our requirements without causing a mutatting table error. Cheers, APC Like Show 0 Likes(0) Actions 5. Re: how to overcome mutating table error. riedelme Feb 12, 2007 3:53 PM (in response to 544866) Using the AUTONOMOUS TRANSACTION pragma in the trigger's PL/SQL block is one way to got - probably the easiest - but has some considerations. Under just the wrong conditions it can cause data issues. With luck you won't encounter these conditions, though. There are