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

Maybe you should check how many rows were affected by DELETE statement... The only problem is that you must check for this stuff after each DELETE in each programming environment.

As I suppose in order to implement server-side logic you have two ways: 1. Create stored procedures which handles all deleting and raises error if such record was previously deleted. So [except these procedures] you should never execute DELETE agains these tables.
2. Store in each dynamic table field called fE STATE: - 1 (or null) - in case record is not deleted - 0 - in case record is actually deleted. Then you create view for each table (SELECT * FROM table_name WHERE state = 1 [state is null]) and execute all DELETE operation against these views. Create INSTEAD OF DELETE trigger which would perform necessary checkings and update STATE to 0.
Of course this solution has drawbacks, but we use this schema for several years in several projects and are pretty happy with it.

Hope it helps.
With best wishes,
Dmitry Sirotkin.

Adam Lipscombe wrote:

> 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 - 06:16:48 CDT