Oracle Database Management Strategic Directions

1. Best Practices for managing Oracle database servers.
2. Oracle Fusion Middleware products like J2EE, ADF, XML, BPEL, SOA, Web Services, Discoverer...).
3. Oracle Application Servers and Apache.

 
 
Wednesday, January 10, 2007

Dealing with Oracle Mutating Table Trigger Error

Oracle developers can encounter the "Mutating Table" error when writing row level triggers. This error occurs when a row-level trigger on a table, references the same table it is modifying in the trigger code. Oracle tries to protect you from bad design. The table the trigger is tied to, is the mutating table.

ORA-04091: table is mutating, trigger/function

Developers from other databases may be a little surprised when they get this error. This occurs due to how Oracle deals with read consistency. Be aware that the mutating table error is trying to protect you. Be careful with any of these solutions that you are not creating data integrity issues with your solution.

Different ways of dealing with this include:
  1. Perform the solution using a PL/SQL table or a Global temporary table. A statement level trigger can then perform the modification. Statement level triggers due not have to deal with the mutating table error, only row level triggers. Tom Kyte has a good example of this.
  2. Autonomous transactions allow a row-level trigger to run a block of code that runs in its own transaction environment. Or to call a PL/SQL procedure that runs in a separate transaction environment that does not cause a mutating table trigger error. Make sure that you have not created any design issues with these solutions. Oracle is protecting you for a reason. The autonomous trigger or PL/SQL block does not understand that it is running as part of a row-level trigger because it has a completely separate transactional context. The autonomous PL/SQL is a good solution when you only need to read the mutating table. The following PL/SQL code is used to set up autonomous blocks of code.
PRAGMA AUTONOMOUS TRANSACTION

Go to the George Trujillo blog and click on Video podcasts for an example of using autonomous transactions.

posted by Unknown @ 11:55 PM   0 comments

 

0 Comments:

Post a Comment

<< Home

 
 

Unknown
profile

  
 

Popular Classes

  • Oracle11gR2 New Features

  • Intro to Java Programming
  • Intro to Linux for Oracle
  • Intro to PL/SQL Programming
  • Oracle11gR2 Fusion Dev Wkshp I

     

    		•   
       
     

    The Trubix Blog is focused on discussions on strategic directions in database technology and the challenges Oracle technologists are addressing today and in the future. This site will focus on issues and challenges of database management that cannot be resolved with a code snippet. There are already a lot of great websites out there with tons of code samples. We would like to facilitate more discussions on issues Oracle technologists are dealing with today that a quick search on the Internet cannot solve. There will also be a group of recognized industry leaders that will also participate in this blog. This blog is an extension of the Tim Tam Group, an international group of industry leaders that meet once a year to discuss strategic directions in the industry.

     

    		  
       
     

    Powered by Blogger