Mutating Tables and Triggers - Help!
Date: 1996/06/25
Message-ID: <4qneqb$lss_at_decius.ultra.net>#1/1
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.