Re: mutating trigger

A copy of this was sent to Jiri.Felcman_at_no.abb.com (if that email address didn't require changing) On Tue, 04 Jan 2000 13:04:41 GMT, you wrote:

>Hi,
>
>long time I have been having problems with mutating triggers. Up to v.
>7.3 it is not allowed to use SELECT from "X" on triggers for table "X".
>For example: If I use cursor with select from table "x" in trigger
>before_update for table "X" and the trigger is then triggered I receive
>following error message: table "X" is mutating, trigger/function may
>not see it. Is this feature improved in higher ORACLE versions or is
>there any other possibility how to solve this without having to program
>anything on client?
>

the mutating table error (designed to prevent you from seeing totally inconsistent results) is still in place, albeit relaxed, in Oracle8 and up (some restrictions on constraining tables are removed).

In Oracle8i, there is a chance you can use autonomous transactions -- but they will see the table as it existed BEFORE the triggering statement ever executed (they'll see only what was committed to the table before they started)....

You can also look at http://osi.oracle.com/~tkyte/Mutate/index.html for some info on how you can defer your processing until an AFTER trigger since it is only the BEFORE/AFTER FOR EACH ROW triggers that have issues with mutating tables.

>Thanks for answer
>
>Jiri
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Jan 04 2000 - 07:39:38 CST