Re: How to implement optimistic locking with a delete trigger?

"Adam Lipscombe" <adam_at_cobar.fsbusiness.co.uk> wrote in message news:<9npu2v$4sd$1_at_news6.svr.pol.co.uk>...
> Folks,
>
> I am designing a Java server accessing Oracle 8.1.7 via JDBC and stored
> procedures.
>
> The system uses an optimistic locking strategy with the row version
> identifier being a number column called "transaction_control_number" (tcn).
> This column is present in all dynamic tables. The tcn value is read when the
> row is read, and the value passed around the system like a cookie. When it
> comes to update time, the proposed new tcn value is compared to the current
> tcn value by an update trigger. If they are the same (i.e. the row has not
> changed) all is well and the update proceeds. If they are different then
> another user has updated the row since the data was read and a concurrency
> exception is raised.
>
> Pretty straightforward stuff.... The update trigger looks like:
> ---------------
> create or replace trigger tr_organisations_bu
> before update
> on organisations
> for each row
> begin
> if :new.transaction_control_number <> :old.transaction_control_number + 1
> then
> raise_application_error(-20001,
> 'Concurrency Failure');
> end if;
>
> :new.transaction_control_number := dbms_utility.get_time;
> end;
> -------------------
>
>
> But, how can this be implemented in a delete trigger?
>
> The system needs to throw a concurrency exception if user A attempts to
> delete a row when user B has updated it since user A read it.
> AFAIK, there is no " :new." or ":old." notation in delete triggers.
>
>
> OK, I could re-read the row in the "delete" stored proc and then compare
> the tcn values. But this only works if rows are always deleted via the
> delete stored proc. I need to handle scenarios where the row could be
> deleted via various mechanisms (Java server, Forms, Sql-Plus etc.) A trigger
> would seem ideal but how to do it?
>
>
> Many thanks in advance.....
>
> Adam
>
> Adam Lipscombe
> Software Engineer
> Qualified Computer Services,
> Cornwall. UK.

Adam, I am not experienced at web system design so if my questions are off I will aplolize in advance but Oracle table delete triggers do provide access to the row data via :old so at the database level a before delete trigger could compare the transaction control number in the row against a system variable provided via a package and updated by your application prior to the delete statement. OR you might use a select for update in the application and compare the TCN retrieved via the one in the application before performing the delete where current of operation.

In your update trigger example why are you updating the TCN value before the update takes place. Wouldn't you want the :new.tcn to be used in the update since you just verified it was valid?

Like I said I may not understand the problem correctly, but I wanted to raise the issue on delete triggers :old, and track this concept for future reference.

• Mark D Powell --