Oracle Triggers / Locks

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.

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 Received on Tue Jun 29 1999 - 04:16:13 CDT