Re: prevent 2 users update same row?

In order to get round this 1st add a date column to your table called timestamp. 2nd create a trigger on the table that inserts the sysdate into this column on insert and update.

Put your update statement into a Stored Proc and do a select on the timestamp column of the record you want to update, and compare the time of your time stamp to the one you have just selected. If the time stamps are the same then proceed if they are not inform your user of the changes made.

Hope this helps.

Regards,

William.  

EuroNet*Internet BV, http://www.euronet.nl/ Wm. G. Urquhart, Oracle DBA
Muiderstraat 1, Postbus 11095, 1001 GB Amsterdam T:+31 20 5355263, F:+31 20 5355400, E: williamu_at_nl.euro.net M:+31 06 28206038 ICQ: 56047340 In article <39194900.A7A716D8_at_mgm-edv.de>, gerhard.eberlein_at_mgm-edv.de says...
> I'm not sure, where is your goal. For the named statements of course there
> is no other way to work as you described. You try to change to columns by
> statement T4 and close the transaction immedeately. wo things has to be
> done:
>
> 1. Set the database server to the correct isolation level.
> 2. Use SELECT FOR UPTDATE with a following UPDATE CURSOR
>
> and you'll see: it works
>
> Best regards
> Gerhard
>
> mergler_at_my-deja.com schrieb:
>
> > The scenario I am trying to prevent is as follows:
> >
> > T1 User A 'SELECT * from TBL WHERE ID=1'
> > T2 User B 'SELECT * from TBL WHERE ID=1'
> > T3 User A 'UPDATE TBL SET COL1='FOO',COL2='FOO-TOO' WHERE ID=1' then
> > COMMIT
> > T4 User B 'UPDATE TBL SET COL1='FOO-FOO',COL2='' WHERE ID=1' then
> > COMMIT
> >
> > T4 has just overwritten the update from T3. Any changes made by T3 are
> > gone. Is this a problem? Yes, according to my customer! :(
> >
> > To me this is quite an obscure occurence of events and I question the
> > real need to prevent this (in our application, its not likely to happen
> > unless you *try* to do it), but nevertheless we must prevent this.
> >
> > I have tried using sequences within triggers and using the SELECT FOR
> > UPDATE clause and I have found that I cannot prevent T4 from occuring.
> > I could be doing something wrong of course, but I would sincerely
> > appreciate any insight, past experience, or voodoo and/or magic tricks
> > that can prevent this scenario from occuring. Thanks!
> >
> > Regards,
> > Jeff Mergler
> > QSI Corporation
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
>
> --
> Gerhard Eberlein
> Radspielerstraße 14
> 81927 München
> Telefon: +4989/920915-21
> Mobil: +49172/8861287
 

--