Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Home
Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients

 

Free Oracle Tips


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

Remote Oracle

Custom Oracle Training

 

   
  Oracle Tips by Burleson

PL/SQL, Triggers, and Mutating Tables

A mutation table is defined as a table that is changing.  But in dealing with triggers, it is a table that has 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 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.  The insert to the child table caused the foreign key to validate the data on the parent (which fired the trigger) causing the insert of the child table to result in a mutating table error on the parent table.

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 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 table that it has read from.  This is the mutating table error issue.

The fact that a trigger can cause other triggers to fire is an important item to remember.  A trigger that causes other database events to execute triggers can cause the database crash.  For example, the database can capture server errors by defining a trigger on the database server error event. 

But if this trigger causes a server error, the database will spin in a loop, with each firing of the trigger causing the error, firing the trigger again, and again, and again.  The only way to regain control of the database is to disable the trigger.


The above book excerpt is from:

Easy Oracle PL/SQL Programming

Get Started Fast with Working PL/SQL Code Examples

ISBN 0-9759135-7-3   

John Garmany 

http://www.rampant-books.com/book_2005_1_easy_plsql.htm


Download your Oracle scripts now:

www.oracle-script.com

The definitive Oracle Script collection for every Oracle professional DBA

 
 
 

Oracle performance tuning book

 

 

Oracle performance tuning software

 
Oracle performance tuning software
 
Oracle performance Tuning 10g reference poster
 
Oracle training in Linux commands
 
Oracle training Excel
 
 
 
 

 

email BC:


Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.