Mutating table

From: Zachary Agatstein <Zachary.Agatstein_at_atsc.allied.com>
Date: Thu, 10 Dec 1998 14:01:02 -0500
Message-ID: <74p5ta$2aeu1@tmpsp002.tmpprv.allied.com>

Hi,

I have a table that is used for periodic logging. The logging application inserts a burst (currently 369) of records every minute. This will be changed to once in 5 minutes. On the other hand, I need to delete old records periodically also.

So, I created an AFTER INSERT ROW trigger on the log table, where I first tried to delete a number of old records. As you know, this did not work, as the table was declared "mutating", and neither of the two operations (inserting new records and deleting old ones) worked.

So, smart ass I am, I created a dummy table, and let the original trigger update the dummy, and then created a new trigger, which would fire on update of the dummy table and proceed to delete the records from the original log table. But Oracle is smart enough to see through this, and complains about "mutation" just as well.

What's a good strategy to implement this?

Thanks

Zach Agatstein Received on Thu Dec 10 1998 - 13:01:02 CST