June 12, 2003 at 3:12 am
I have a situation where I want to select a record to display it on a web page with the intention of updating it.
Once it has been selected I want to signal that it can still be read but cannot be edited or deleted.
Once the edit has been done I want to release the record so other people can edit it.
Within the context of SQL Server I know how to lock records within a single stored procedure but this is effectively a stored procedure to retrieve the record and a seperate stored procedure to update the record. How do I deal with this?
Also, is there anyway that I can identify that a record is being edited so that I can display an icon on a web page against that record?
Thanks
June 12, 2003 at 11:32 am
We have done something like this in a Data Entry app. There are a couple of ways to do this that I can think of (we used the 1st method):
1) Use a field in the table that you increment each time the record is UPDATED. When you SELECT the data to the application, bring back that value and hold onto it until your ready to save the record, then pass that value back to the SAVE procedure. The SAVE proc will compare the original value to the current value in the table. If they are different, send back an error/warning to the user saying that "someone else has saved the record while you had it on the screen." You can decide how you handle the confict; either tell the user they must refresh the record, or allow them to overwrite the other person's changes. A nice side-effect of this method is you get a built-in "change counter." The downside is you have no way of knowing who else has the record open at that time.
2) Another method is to use a bit field to flag that the record is "LOCKED" after a user SELECTS the record, then allow a user to view, but not change a record if it is "LOCKED". The problem with this method is: if a user just closes the browser, you have no way to unlock the row. It is stuck in a locked mode. You could just use the locked bit as a guide for display reasons, but use method 1 to enforce concurrency.
-Dan
-Dan
June 12, 2003 at 11:44 am
I've found that a locked bit isnt enough, I want to know who locked. Lots of times an app/connection will drop, leaving the lock bit set. We go with the userid of the user locking the record, either NT login or some internal login. To update you have to be that user, which means if you do drop the connection and come back later, that user can still edit it.
Andy
June 14, 2003 at 6:55 pm
I guess that when you get to this stage of affairs, what you are really talking about is writing your own locking table, and keeping track of 'system locks', rather than 'data source locks'. You could implement a table, where by you have to record user info regarding current activity, in the same way that many people will implement application security, but then you have the increased overhead of making all these extra database calls. Interesting thought though.
Makes you wonder why MS does not record have the 'user activity data' that so many of the people on this site are requiring? prob best not to ask those kinds of questions though.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply