Re: Mutating table error

oweishaar_at_phelpsdodge.com (Oliver Weishaar) wrote in message news:<3fac8d9.0205221443.18467f48_at_posting.google.com>...
> I have a table that tracks events and event sequences (primary key is
> event_id, seq_id). When I do an insert, I need to determine if the
> sequence is min or max for the event, so I wrote a row-level insert
> trigger that does the following:
>
> select min(seq_id), max(seq_id)
> into v_min, v_max
> from events
> where event_id = :new.event_id
>
> The problem is that since this is a row level insert trigger, I get an
> error stating that the events table is mutating. Is there a way in
> Oracle to get around something like this?
>
> Any help is greatly appreciated.

Try "#pragma autonomous_transaction" in your trigger body. I haven't tried using autonomous transactions to avoid mutating tables, but in theory it should work.

If that doesn't work, try modifying the query to exclude the row being inserted. You might try adding " and seq_id != :new.seq_id" to your offending statement. Received on Thu May 30 2002 - 08:03:03 CEST