Re: Locking

I think you should look at a pre-row update trigger.

If a normal process tries to update a record, then check for the matching interface record and raise an error if it exists.

To allow the 'batch update' mechanism to work, you need a global packaged variable set in the batch which says 'this is a batch run', and the pre-update test should actually check the value of this variable before it does anything else.

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases

Publishers:                 Addison Wesley Longman
Book bound date:     8th Dec 2000
See a first review at:
http://www.ixora.com.au/resources/index.htm#practical_8i

Graham Atkinson wrote in message <974453631.207462_at_ernani.logica.co.uk>...


>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.

>>

>

>