My 2 cents:
Mr. Kyte has correctly described in detail the PL/SQL coding steps
required to avoid this mutating trigger error.
But this requires some reasonable knowlege of PLSQL programming and a
lot of typing.
To help avoid this you can use my tool SQLPlusPlus, it will generate a
PL/SQL package along with a set of database triggers with all the code
to implement this functionality on database server side. Download free.
kind regards,
M. Armaghan Saqib
- SQL PlusPlus : Add power to SQL Plus command line
- SQL Link for XL : Integrate Oracle with XL
- Oracle CBT with sample GL (Triggers and Forms Source Code included.
Download free: http://www.geocities.com/armaghan/
Thomas Kyte <tkyte_at_us.oracle.com> wrote in message
news:emt37ssf487kdcj5ilqikblvnp5sls7ui9_at_4ax.com...
> 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
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Wed Jan 05 2000 - 00:59:18 CST