Re: Triggers and Mutating tables.

Scott Urman, Oracle8 PL/SQL Programming, page 335: "If an INSERT statement affects only one row, then the before and after row triggers for that row do not treat the triggering table as mutating. This is the only case where a row-level trigger may read from or modify the triggering table. Statements such as INSERT INTO table SELECT... always treat the triggering table as mutating, even if the subquery returns only one row."

Hth

Klaus
"Jim Ley" <jim_at_jibbering.com> schrieb im Newsbeitrag news:399821d5.29105111_at_news-east.usenetserver.com...
> On Mon, 14 Aug 2000 18:07:30 +0200, "Sybrand Bakker"
> <postbus_at_sybrandb.demon.nl> wrote:
>
> >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.
>
> But what is the difference, between a "before insert" and a "before
> update" why doesn't it have the same problem with the insert?, the
> update shouldn't've happened when the trigger fires, and therefore the
> select should be on the old unmutated table shouldn't it?
>
> >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.
>
> I guess I'll have to try that if there aren't any other options.
>
> Thanks,
>
> Jim.
>
Received on Mon Aug 14 2000 - 15:59:42 CDT