Re: Oracle Triggers / Locks

A copy of this was sent to "Jonathon Marshall" <marshallj_at_siemens.co.th> (if that email address didn't require changing) On Tue, 29 Jun 1999 16:16:13 +0700, you wrote:

>Hello all
>
>
>following extensive testing I have come to the conclusion that whareas it is
>possible to assign values to the :new table columns within a row by row
>trigger (before insert or update), it is utterly pointless to do so since
>the record then becomes locked disallowing any subsequent edits.
>

no, not at all. the very act of INSERTING the record locked it for your session to begin with. the trigger isn't adding any locks at all.

What you are seeing is due to the CLIENT application and how it is locking the data.

Lets say in the client application you inserted:

   insert into T ( sa_id ) values (NULL);

as far as the client was concerned (sounds like you are using forms based on the error message), the sa_id field should be NULL -- thats what you put in after all. The client inserts the row for you. Your database trigger *changed* the value of sa_id from NULL to something else. Later on in your application, you attempt to modify the record in the block. The record you have on your SCREEN does not match what is in the database however -- if we were to take the record from your screen and put it back -- we would LOSE the work performed by the database trigger.

You must requery the data in the client so the client has the most current informatoin. Otherwise, you would suffer from a lost update -- where your changes made in the original insert are lost becuase the client just blinding overwrote them.

>for example
>
>trigger body for before insert on a test table
>
> BEGIN
>
> if :new.sa_id is null then
>
> select sa_idseq.NextVal,USER,SYSDATE
>
> into :new.sa_id,:new.SA_CREATEDBY,:new.SA_CREATEDON
>
> from Dual;
>
> end if;
>
> END;
>
>Seems to work ok but next edit of same records reports a lock in place on
>the
>record
>
>I Recieved a suggestion to implement the above as follows :
>
>(where new.sa_id is null)
>
>
>declare sa_idval integer;
>
>BEGIN
>
> select sa_idseq.nextval into sa_idval from dual;
> :new.sa_id := sa_idval;
> :new.sa_createdby := user;
> :new.sa_createdon := sysdate;
>
>END;
>
>Both of the above still appear to lock the record, even though the trigger
>should not be reiteratively called (due to the where clause ). Infact any
>trigger which assigns values to :new columns causes locking to occur
>
>The locking occurs when I use the oracle navigator to insert a record (the
>addition itself works fine) however when I refresh the recordset and attempt
>to update the same record, I recieve an error message stating that another
>user has updated or deleted the record. The same locking occurs on the
>client I am developing in VB.
>
>I know that the above may be performed at the client (within a before post
>event), however It occurs that the above requirement is so simple (and
>desirable)
>either I am doing something wrong or certain functionality within oracle is
>quite
>simply pointless
>
>(I know which one of these I believe :-)
>
>Any Ideas on this one ?
>
>Regards
>
>Jon
>
>Previous mail follows
>============================================================================
>========
>
> Hi all,
>
> I have created a Simple before insert trigger (for each row) as
>follows on
>
> personal oracle 8 (using the Oracle 8 Navigator)
>
>
>
> BEGIN
>
> if :new.sa_id is null then
>
> select sa_idseq.NextVal,USER,SYSDATE
>
> into :new.sa_id,:new.SA_CREATEDBY,:new.SA_CREATEDON
>
> from Dual;
>
> end if;
>
> END;
>
>
>
> the problem I am experiencing is that after I have inserted a
>record, upon
>
> trying to edit it I get a message that another user has changed or
>deleted
>
> the record (in other words, the trigger itself must have locked the
>record)
>
> How can I prevent the error from happening ?
>
> Regards
>
> Jon
>

--
See http://govt.us.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 Jun 29 1999 - 09:21:10 CDT