Re: Locking

Our application (a PowerBuilder App) is being changed to accept new records from a third party source. This is handled by having a bunch of interface tables matching the tables in our application. The third party can effectively populate our database by entering records in the interface tables which we then pick up.
The problem ocurrs when we have accepted the new record, modified it and copied it back to the interface tables for the third party to modify. The record must be read-only on our application at this point allowing users to view the records. The third party make amendments and update the interface tables and we copy back the changes. At this point we delete the interface table record and the record in our application again should become editable. This prevents the record been simutaneously modified on our tables and the interface tables.
I have a LOCK column on our tables which is set accordingly but don't really want to go through the process of updating all our application code to check for a 'Y' and make the windows in the application read-only. I thought there might be something clever I could do on the server (Oracle 7.3.4).
The third party may take a while updating their records and in the meantime the database may have been shut down and restarted - which adds to the problem.

Sorry for the loooooong explanation.

Graham.
"David Grzebien" <dgrzebie_at_columbus.rr.com> wrote in message news:3A149068.DA0102DD_at_columbus.rr.com...
> Out of curiousity, what is the purpose of "locking" the row even when the
> database starts up?
>
> One thing you could do to implement this "locking" is to introduce a new
> column to the table (ie LOCK_IND) that would be a 'Y' or an 'N'. When you
 want
> a row to be locked, set the value to 'Y', and when you want it unlocked,
 set it
> to 'N'. Then you could place an UPDATE trigger on the table that checks
 the
> value of LOCK_IND on the table. If the value is 'Y', then raise an
 exception
> in the trigger indicating that the row is locked.
>
> Hope this helps,
>
> Dave Grzebien
> Expert Technical Consultants, Inc
> dave_at_etci.net
>
> Graham Atkinson wrote:
>
> > Is it possible within Oracle to lock a single record on a table so that
 any
> > application attempting to update that record would get an error
 retruned.
> > This lock needs to be present even if the database has been shutdown and
> > restarted (although I could use a DBMS_JOB to kick the lock off again)
> >
> > Thanks,
> >
> > Graham.
>
Received on Fri Nov 17 2000 - 03:33:50 CST