Re: Triggers and Mutating tables.

Let's look at what is really happening, maybe that clarifies it to you.

1 You issue an update on your table
2 the trigger fires
3 within the trigger you select from you table

The last step fails because you can't select from a table which is *already* mutating. It is mutating because of your update statement. The trigger doesn't mutate it, the trigger selects from it and Oracle can't guarantee the results.
The usual workaround is described in the link you followed. The basic idea is
create a package with a pl/sql table buffering the primary keys affected before statement: reset the table
before row : store the primary key in the buffer after statement: loop through the array and do whatever you want. However in your case you probably would try the after row event. Although in that case the update already has been done you will still be able to cancel it.

Hth,

Sybrand Bakker, Oracle DBA

"Jim Ley" <jim_at_jibbering.com> wrote in message news:399802e3.21181406_at_news-east.usenetserver.com...
> On Mon, 14 Aug 2000 14:09:01 GMT, jim_at_jibbering.com (Jim Ley) wrote:
>
> >The trigger shouldn`t change the table, so I don`t know why it should
> >mutate it. I wondered whether you could tell me why I get this error
> >and a way around it?
>
> Sorry, forgot to add, I've looked at
>
> http://govt.oracle.com/~tkyte/Mutate/index.html
>
> but don`t understand why the table should be mutating since it does
> not change the DB at all, only selects from it.
>
> Jim.
Received on Mon Aug 14 2000 - 11:07:30 CDT