Re: Mutating table

In article <74p5ta$2aeu1_at_tmpsp002.tmpprv.allied.com>,   "Zachary Agatstein" <Zachary.Agatstein_at_atsc.allied.com> wrote:
> 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
>
>

Basically, you cannot alter the table associated with a trigger during the after-insert or after-update. This is because this would (could) cause yet another insert or update, which re-fires that same trigger, which etc.

An alternative is to schedule a job ( dbms_job package) to alter the logging table on a periodic basis. The job can be set to run daily or hourly or whatever time is reasonable. The job is controlled by the database and will happen at the interval that you give it.

Mike K.
--
Michael Krolewski
mikkro_at_hbsi.com
All opinions are my own, and do not represent my employer.

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Fri Dec 11 1998 - 20:28:09 CST