Re: Mutating Tables and Triggers - Help!

Bob Blizard wrote:
>
> 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.
>
> Any and all suggestions considered. Replies via email appreciated,
> since I don't get to this newsgroup as frequently as I'd like.Bob,
Check out http://www.revealnet.com/
There is an excellent article on triggers in this site. The article has a very good example of a solution for mutating/constraining tables. Goodluck,
Kumar. Received on Wed Jun 26 1996 - 00:00:00 CEST