Re: Usage of RowId... -- optimistic locking
Date: 1998/04/16
Message-ID: <6h5i92$9ts$1_at_ha2.rdc1.md.home.com>#1/1
Thanks!! This is the same approach that we are no taking.
Brian K. Lawton (mailto:Lawton_at_RDAConsultants.com) RDA Consultants Limited (http://www.RDAConsultants.com)
Saad Ahmad wrote in message
<01bd68e1$f70d0d80$2207030a_at_sahmad-pc.mfa.com>...
>Consider table tab1
>
>It has columns
>PK_COL
>COL1
>COL2
>
>For this support, we will add another column
>update_version number null;
>
>trigger tab1_br before insert or update of pk_col, col1, col2 (NOT
>update_version)
>for each row
>if inserting then
> -- we are always ok here
> proc_update_version ( :new.update_version );
>else
> -- now we have a problem. did someone else change
> -- this row while i had the data on the form?
> -- i cant query this table because i am in before-row trigger
> add_to_a_plsql_table ( :new.pk_col, :new.update_version );
>end if;
>end trigger
>
>trigger tab1_bs after update (statement level)
>begin
> loop on plsql_table_of_before_row
> select update_vrsion into :v_version from tab1 where pk =
>pk_from_tab;
> -- this will give us the u_version for this row from the table.
> -- if no one changed it on us, while we were on the row, we should
>still
> -- have the value that is stored in the array
> if v_version != plsql_table.update_version
> then
> let package.msg := 'Information Changed for '|| pk_info
> raise a_named_exception_bound_to_20001;
> else
> -- we know that we are ok now
> update tab1 set update_version = proc_update_version (
>plsql_tab.version )
> where pk = plsql_tab.pk;
> -- dont worry the beforfe statement won't fire again
> end if;
> end loop;
>end trigger;
>
>procedure proc_update_version ( io_update_version (in/out) ) is
>begin
>if io_update_version is null
>then
> io_update_version := 0;
>else
> io_update_version := mod ( io_update_version, 100000 );
>end if;
>end
>
>CLIENT SOFTWARE
>-------------------------------
>Forms or PowerBuilder or whatever ...
>
>Create a centralized routine say AFTER_STORE, that does the following
>if ( sqlcode_from_oracle != 0 )
>then
> if sqlcode = -200001
> show_message_to_user ( package.msg [from PL/SQL package] )
> end if
> rollback;
>else
> commit;
>endif
>
>Scenerio 1 - Update was supposed to succeed
>Retrieve the data for a PK value ( say the u_version was 10 and PK PK1 )
>Update it on the form
>Store it (implying that client software issues the actual update
>statement)
> - before row fires, stores in the pl/sql array PK1/10
> - <does whatever else the trigger was supposed to do>
> - after statement eventually fires
> - table queried and the u_version was still 10, so make it 11
>call AFTER_STORE
>
>Scenerio 2 - Update was supposed to fail
>Retrieve data for PK value (u_version 11, PK PK1)
>Update it on the form
> In another session retrieve PK value ( u_version 11, PK PK1)
> Update
> Store (u_version now 12 because of triggers)
> commit
>Store it
> - before row saves PK1/11 in the array
> - after statement queries tab1 and sees that value is 12 now so raise
>error
>AFTER_STORE will show the message and rollback
>
>
>Summary
>--------------
>The triggers seem to be a lot of work, but they can be generated quite
>easily and latest versions
>of Oracle (7.3.4 or 8) do allow for multiple triggers on same event. So
>the creation of the triggers
>should not pose a huge problem.
>
>Some FrontEnd softwares may already allow optimistic locking, but the
>trigger approch has the
>potential advantage of working with all technologies.
>
>
Received on Thu Apr 16 1998 - 00:00:00 CEST