Check update values?

[CHRISWYA]

UPDATE calls
SET close_date = TO_DATE(p_close_date, 'DD/MM/YYYY')
WHERE calls.ID = TO_NUMBER (p_callerID);
(AND...)

Hi there

just wondering if anyone can help me i have basically finished my application and am adding validation (etc). In my program i have many small update statements as above, however i would like to add an additional AND clause to these statements.
The clause i require would be used if for example a user had entered an update screen then gone for lunch, returns and then goes to update the record. However during this time the record may have already been updated. Therefore i want a clause that checks that the passed value(p_close_date) is still the value of (p_close_date) at the time the update occurs.

If anyone has any ideas this will be greatly appreciated!!

 

[PHV]

Perhaps this ?
AND close_date IS NULL

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[CHRISWYA]

Not exactly what im after but can see wht ur thinkin.

What may happen in the application is that multiple users can update a record and in this example its close_date. So if a user enters an update screen and leaves it while another user enters that record and changes the close_date and updates it.
Therefore if user1 returns and goes to update the close_date the value shown on his screen is incorrect. thereofore i require a clause that checks close_date matches the database value at the time an update occurs.

 

[PHV]

Why not simply add another parameter to your SP like p_old_date ?
Or even better (I think) add a timestamp column you can test to be sure the record is unchanged since the select.
BTW you don't play with any lock mechanism ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[fredericofonseca]

Add a timestamp column.
When the user starts "changing" the record on screen you retrieve that value, and when he then issues the "save" command you pass that value to your SP and compare it to the one actually on the record. If diff someone else has updated the record and you should deal with it accordingly.

Using a SP makes it easier, but it is not absolutely required.


PHV,

Many people do not use lock's as if they do they could have the whole company locked out for a few ours just because someone decided to go to lunch on the middle of updating an invoice.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[JarlH]

When you talk about "locks", do you mean *transactions*?

Transaction handling does NOT imply locking. (The ANSI SQL standard does not mention how transaction handling is implemented.)

 

[PHV]

When you talk about "locks", do you mean *transactions*?
Not necessarly. The current record is displayed and the lock (deny write, allow read) is acquired only when the user explicitely wants update it.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[fredericofonseca]

No, not transactions. Lock's as in locking a specific record(s).

search

http://www.deja.com

for many threads on the subject. There is no easy answer there, and how to implement a "locking" mechanism will depend on how the application works.


One of my customers created a "softlocking" mechanism where by no one would be able to change a policy if someone else was changing it.
This was implemented by writting the policy number to a file when starting certain transactions, and then removing it at the very end of it.
If the transaction failed a manual removal of the "lock" record would be required after investigation of failure reasons.

This application was a very good candidate for this. Others aren't.




Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com