What is the best way to permanently mark a record as read-only?

Question

I have an Access 2003 app that connects to a SQL Server 2000 box.

I have a table in which I need to lock down a record along with all related records in different tables. By "lock down", I mean mark them as read-only so that no clients can edit those records unless an admin unlocks them.

Any ideas?

Answer 1

More than likely there isn't an "elegant" way of doing this at the database level. But there are a few routes you could do.

1. Add a "locked" bit field to each table, and when "locking" the parent cascade that value.
2. In conjunction with #1 add a trigger on update and delete, if the flag is set, you can cancel the update or delete.

That is about the only real easy way to enforce it at the db level that I can think of.

Answer 2

Assuming your data is in SQL Server, I would use the SQL Server security if possible first. We typically would not allow any access to tables and then control it through SPs. SPs can have more complex logic to determine whether a particular operation should go through.

If that's not an option, you can always use triggers, check the rows and deny an update or delete that way.

There's a ton of ways to skin this cat.

Answer 3

Just another option to throw out there:

• Add a last_updated column to your table, which is updated by the update trigger
• Create a table, Locked_Widgets (or whatever) which is simply the PK of your base table and the last_updated column and the whole set of columns make up the PK in Locked_Widgets
• Put a non-cascading foreign key from the base table to Locked_Widgets

If anyone tries to update the row the trigger will try to update the last_updated column and the foreign key constraint will cause the update to fail.