Re: Mutating Tables and Triggers - Help!

In article <4qneqb$lss_at_decius.ultra.net>, rblizard_at_bb.cmcsys.com (Bob Blizard) writes:
|> I am trying to figure out how to get around the Oracle limitation on
|> triggers and mutating tables. Here's what I'm trying to accomplish:
|>
|> I have several tables where there is an effective date and also a
|> cancel date. When a new row is inserted with an effective date, the
|> old row for the key columns becomes cancelled, and the cancelled date
|> (in another row of the same table) has to be set to the effective date
|> - 1. (I am using the term 'key columns' loosely - other columns also
|> figure into the key attribute.)
|>
|> If I write an insert trigger which sets the cancel date for the older
|> instance of the 'key columns', I get a Mutating Table error, and the
|> transaction is rolled back. I am currently handling it by writing the
|> key and change information to a journal table, and sweeping through
|> the table every 15 minutes, executing what should be the trigger code
|> for each row in the cursor.
|>
|> Is there a way to outsmart Oracle? The documentation (Concepts
|> Manual?) indicates that 'PL/SQL Tables, Package Variables, or
|> Procedures' may be used to get around this limitation, but gives no
|> examples or suggestions.

This is the best way to solve this - in the row trigger, you record the change you want to make in a packaged variable or packaged PL/SQL table. Then you can update the table in an after statement trigger, which doesn't have the mutating table restriction. I've got an example of this in Chapter 5 of _Oracle PL/SQL Programming_, which you can download from http://www.osborne.com/oracle. Let me know if you have any other questions.

|>
|> Any and all suggestions considered. Replies via email appreciated,
|> since I don't get to this newsgroup as frequently as I'd like.
|>
|>
 

-- 
------------------------------------------------------------------------
Scott Urman            Oracle Corporation           surman_at_us.oracle.com
------------------------------------------------------------------------
Author of _Oracle PL/SQL Programming_ ISBN 0-07-882176-2
Published by Oracle Press - http://www.osborne.com/oracle/index.htm
------------------------------------------------------------------------
"The opinions expressed here are my own, and are not necessarily that of
 Oracle Corporation"
------------------------------------------------------------------------