Re: Locking

Thanks for all your replies :-)

Graham.

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:974457503.3368.1.nnrp-10.9e984b29_at_news.demon.co.uk...
>
> 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.
> >>
> >
> >
>
>
Received on Fri Nov 17 2000 - 05:20:23 CST