How to implement optimistic locking with a delete trigger?

From: Adam Lipscombe <adam_at_cobar.fsbusiness.co.uk>
Date: Thu, 13 Sep 2001 10:26:06 +0100
Message-ID: <9npu2v$4sd$1@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. Received on Thu Sep 13 2001 - 04:26:06 CDT