Re: Mutating table

Seems like you could use table level trigger instead of row level. Just don't specify 'FOR EACH ROW'.
For table level trigger, you're not gonna get such problem as 'mutating table'.
But you'll loose opportunity to access :OLD and :NEW values, of course. If you don't need them - use table level trigger and you're done.

Zachary Agatstein 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

--
Igor V. Kouznetsov
Software Engineer
UNIF/X Inc.
67 Wall Street Suite 2411 New York, NY 10005 phone: (212) 406-1400
fax: (212) 406-1401
http://www.unifx.com
mailto:igork_at_unifx.com Received on Thu Dec 10 1998 - 15:56:45 CST