Avoid Mutating Trigger Error Oracle
Contents |
Load Balancing Mobile Devl Networking News Open Source Oracle Outsourcing Performance Tuning Protocols Security Sharepoint SQL Server Technology Unix Virtual Server Visual Studio VMWare Windows Windows Vista Questions? Please send your wish list of things that you would like us to write about or if you have suggestions how to resolve mutating trigger error in oracle to help improve this blog site. You can send all questions/suggestions to: Blog Support Archives Archives how to avoid mutating error in oracle with example Select Month May 2016 (1) April 2016 (1) March 2016 (1) April 2013 (1) February 2013 (1) August 2012 (1) April 2012 (3) March
Mutating Trigger In Oracle Example
2012 (6) February 2012 (2) November 2011 (1) July 2011 (3) June 2011 (1) April 2011 (1) March 2011 (1) February 2011 (7) January 2011 (5) December 2010 (1) October 2010 (1) August 2010 (3) July 2010 (3) June
Mutating Table Error In Oracle With Example
2010 (14) May 2010 (5) April 2010 (4) March 2010 (5) February 2010 (6) January 2010 (10) December 2009 (10) November 2009 (5) October 2009 (7) August 2009 (1) July 2009 (18) June 2009 (30) May 2009 (24) April 2009 (35) March 2009 (49) February 2009 (41) January 2009 (30) December 2008 (26) November 2008 (32) October 2008 (25) September 2008 (20) August 2008 (32) July 2008 (31) June 2008 (22) May 2008 (27) April 2008 (27) March 2008 (32) mutating trigger in oracle 10g with example February 2008 (33) January 2008 (29) December 2007 (23) November 2007 (30) October 2007 (33) September 2007 (29) August 2007 (33) July 2007 (32) June 2007 (41) May 2007 (32) April 2007 (37) March 2007 (56) February 2007 (97) January 2007 (37) Blog Stats 6,850,413 Views Email Subscriptions Subscribe to Systems Engineering and RDBMS via Email RSS Subscriptions Subscribe to Systems Engineering and RDBMS via RSS Feed Recent Posts SQL Server 2016 - RTM on June1st Blockchain SSMS and .Net Framework4.5.1 SQL Server Virtualization Custom Search You can use our customized Search Engine based on Google's Co-op to narrow down your searches and get focussed results. Calendar June 2009 M T W T F S S « May Jul » 1234567 891011121314 15161718192021 22232425262728 2930 Top Posts Removing un-wanted text from strings in Oracle SSIS: Exporting data to a text file using a package Effectively Repairing Windows 2003 Server Mutating table/trigger error and how to resolve it COALESCE(), ISNULL(), NVL() db2exfmt – Command to format the explain plan information in DB2 LUW Back to the basics: Declaring procedures and functions in anonymous PL/SQL block Returning a record set from a Function in Oracle, SQL Server and DB2 Functional difference between "NOT IN" vs "NOT EXISTS" clauses Checking database parameters in Oracle Blog Roll Arup Nanda Ask Perf Cary Millsap's blog DBAzine Blogs EMR & HIPAA GigaOM IBM Blogs IR Web Report ORA FAQs
Social Links Printer Friendly About Search 8i | 9i | 10g | 11g | 12c | 13c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Is Mutating Trigger/function May Not See It
Home » Articles » 9i » Here Mutating Table Exceptions Mutating table exceptions table is mutating trigger/function may not see it occur when we try to reference the triggering table in a query from within row-level trigger code. In this oracle mutating table example article I'll present examples of how a mutating table exception might occur and simple methods to get round it. Test Schema Mutating Table Demonstration Solution 1 (Collection in Package Variable) Solution 2 https://decipherinfosys.wordpress.com/2009/06/22/mutating-tabletrigger-error-and-how-to-resolve-it/ (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 ( id NUMBER(10) NOT NULL, action VARCHAR2(10) NOT NULL, tab1_id NUMBER(10), record_count NUMBER(10), created_time TIMESTAMP ); https://oracle-base.com/articles/9i/mutating-table-exceptions 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 TRIGGER tab1_ariu_trg AFTER INSERT OR UPDATE ON tab1 FOR EACH ROW BEGIN IF inserting THEN trigger_api.tab1_row_change(p_id => :new.id, p_action => 'INSERT'); ELSE trigger_api.tab1_row_change(p_id => :new.id, p_action => 'UPDATE'); END IF; END; / SHOW ERR
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 http://dba.stackexchange.com/questions/5432/what-are-the-causes-and-solutions-for-mutating-table-errors the company Business Learn more about hiring developers or posting ads with us Database Administrators http://stackoverflow.com/questions/19292020/how-to-avoid-getting-oracle-mutating-trigger-error Questions Tags Users Badges Unanswered Ask Question _ Database Administrators 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. 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 mutating trigger are voted up and rise to the top What are the causes and solutions for mutating table 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 error in oracle 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 8:46 parmanand 107238 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 th
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 How to avoid Getting ORACLE Mutating trigger error up vote 0 down vote favorite I created the trigger to update the oracle data base table after insert. CREATE OR REPLACE TRIGGER Update_ACU AFTER INSERT ON TBL_ACU FOR EACH ROW BEGIN UPDATE TBL_ACU SET CURRENCY = 'XXX' WHERE ACCOUNT like '%1568'; END ; I inserted record as insert into TBL_ACU values('23','USD','1231568'); I am getting table ORACLE Mutating trigger error. Please help me how to resolve this. oracle share|improve this question edited Oct 10 '13 at 9:36 mbatchkarov 7,82232753 asked Oct 10 '13 at 9:30 user2866268 41 2 So after inserting any row, you want all rows that have an account ending with 1568 to have their currency set to 'XXX'? That's a real requirement? –David Aldridge Oct 10 '13 at 9:45 Yes u are right. –user2866268 Oct 10 '13 at 11:05 add a comment| 2 Answers 2 active oldest votes up vote 0 down vote It would be better to use BEFORE INSERT trigger to do this. Try like this, CREATE OR REPLACE TRIGGER update_acu BEFORE INSERT ON tbl_acu FOR EACH ROW WHEN (NEW.ACCOUNT LIKE '%1568') BEGIN :NEW.currency := 'XXX'; END ; share|improve this answer edited Oct 10 '13 at 9:58 answered Oct 10 '13 at 9:48 Dba 4,79611024 is it right WHEN (NEW.ACCOUNT LIKE '%1568') i think you missed ':' before NEW. WHEN (:NEW.ACCOUNT LIKE '%1568') Please correct me if i am wrong. –user2866268 Oct 10 '13 at 11:52 Yeah sure, before insertion it'll check this. –Dba Oct 10 '13 at 11:58 No, the syntax is correct. You can try with it. –Dba Oct 10 '13 at 12:00 Refer this link for better understanding. docs.oracle.com/cd/B19306_01/appdev.102/b14251/… –Dba Oct 10 '13 at 12:11 add a comment| up vote 0 down vote Well, you cannot modify the table from the trigger if the trigger is called upon modification of that table. There are various solutions to this pr