Lock an individual record to prevent future editing

[Michael57]

I have a data base for a form that is created by one user and passed on by another user for approval. What can be done to lock the record once it has been approved by the second person

 

[jbenson001]

you can set a bit column in the table to 1(true). then in your front end app, check the bit, if it's 1, the record is locked and don't allow editing.

 

[SQLDenis]

>>then in your front end app....
better way to use an update trigger that way fat-finger Sally doesn't do it thru Enterprise Manager or Query Analyzer

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[onpnt]

could you use a constraint on the table to not allow changes to certain criteria? May be a bit messy and unstable though

[sub]____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done[/sub]

 

[Michael57]

Im a novice can someone give me more explaination on the bit column. How do I get it so only one person can access the bit column to change it to one and how does sql know that this means no one else can now edit the record?

 

[TysonLPrice]

You might want to avoid using a single bit and store the user name of the final approval if you haven't already. We use something along those lines and call it "LockUser". Then check the ID of who is logged on against it.

Maybe you have traking data like this already in place. Just a suggestion.

 

[Michael57]

I would like to use the bit column if someone could explain please.

 

[TysonLPrice]

I didn't see an answer sooo...My understanding is your just checking a column with a datatype value of bit for 0 and 1 as boolean values.

 

[Michael57]

Yes I understand I set a value but how does that translate into locking a record. Please be patient with my inexperience.

 

[gmmastros]

Setting a bit flag wouldn't lock the record. In fact, you don't really want to use SQL Server to 'lock' the record. What everyone is suggesting is that you use a bit field to 'pretend to lock' the record. In this case, you must add the conditional logic in to your application such that if the 'locked' bit is true, don't allow the user to modify the record.

Furthermore, Tyson's suggestion of using the UserId to indicate a lock is a little bit better because you would also know WHO locked the record. With the bit column, you would only know that the record was locked, but not who?

Does this make sense now?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom