database programming Q - check before delete

thor

thor

thor

Associate
Joined
6 Nov 2003
Posts
2,138
Location
West London
Just putting the finishing touches to an assessment due in for tomorrow and noticed one of the requirements is to check that a database entry is not in use before deleting it. Anyone know of a nice easy way of doing this?

I'm using ASP.NET/C# with an Access database but any SQL examples will suffice.
 
Depends what you mean by "check that a database entry is not in use before deleting it".

If you mean that someone else has not updated the record between you accessing and deleting it you can create a timestamp field on the table that is updated whenever a record is modified. When you come to delete check the timestamp when you loaded the record against the one now in the DB.

If you mean deny deletion of a record if someone is viewing it then it is more complicated. You'll have to look into locking rows.
 
Fourstar said:
If you mean deny deletion of a record if someone is viewing it then it is more complicated. You'll have to look into locking rows.
Click to expand...
Don't talk to me about row locking, I've spent the last 2 months trying to sort it out on my current project :(

Any decent database manager will handle row locking for you without any intervention but it depends how you're dealing with the rows once they been retrieved from the database. Normally if you do a plain select and take the record into a separate application for viewing then the database transaction will close and the locks will be released. From an application context though the row is still open and shouldn't be deleted so you're probably going to have to come up with a different way of retrieving the data, either with a select for update or by turning off the implicit commit for the transaction and doing it explicitly once the user has finished viewing the record. Both options however have distinct downsides when it comes to concurrency and multiple accesses.
 
You must log in or register to reply here.
Back
Top Bottom