Re: Optimistic Locking

On Fri, 08 Mar 2002 00:39:00 +0100, Stephan Gutschke <stephan_at_gutschke.com> wrote:

>Hi there,
>
>I have got an application which reads data from several database tables
>and stores it in local objects.
>Some time later I want to find out if the data which I read previously
>and which is stored in my local objects is still equal to the one stored
>in the database. In order to accomplish this easily, I thought about
>creating a trigger which always updates an extra column in my tables
>once the table is updated. The extra column would just store a number
>which would be increased by one through the trigger. Ups, there I got
>the ORA 4091 trigger error of mutating tables. Does anyone has a simple
>idea on how to solve this problem?
>
>And another question. Does anyone know why this 4091 exists? I am a
>little confused here, I thought if one declares the trigger with the
>"AFTER" keyword, than that means, after everything (updates, inserts) is
>done, the trigger is fired. Why does oracle think that there is a problem?
>
>Thanx to everyone,
>
>Stephan
>
>

1
- search the archives of this group at http://groups.google.com or http://asktom.oracle.com and don't waste bandwith. This question has been answered numerous times.
-Do not select from the table you are mutating -The 'update' of an extra column can be simply accomplished, by writing
:new.extra_column := <value> in a *before* update for each row trigger

2
Exactly, *AFTER* the update is done, you are trying to update the record *AGAIN*.

Regards

Yo
>P.S.:
>I am using oracle 8i on W2K. Programming with Java.
>

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Thu Mar 07 2002 - 23:36:14 CST