I have an Access 2003 app that connects to a SQL Server 2000 box.
我有一个连接到SQL Server 2000框的Access 2003应用程序。
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?
3 个解决方案
#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.
很可能在数据库级别没有“优雅”的方法。但是你可以做几条路线。
- Add a "locked" bit field to each table, and when "locking" the parent cascade that value.
- In conjunction with #1 add a trigger on update and delete, if the flag is set, you can cancel the update or delete.
为每个表添加一个“锁定”位字段,并在“锁定”父级联该值时。
与#1一起在更新和删除时添加触发器,如果设置了标志,则可以取消更新或删除。
That is about the only real easy way to enforce it at the db level that I can think of.
这是我能想到的在数据库级别强制执行它的唯一真正简单的方法。
#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.
假设您的数据在SQL Server中,我会首先使用SQL Server安全性。我们通常不允许对表进行任何访问,然后通过SP控制它。 SP可以具有更复杂的逻辑来确定特定操作是否应该通过。
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.
有很多方法可以给这只猫上皮。
#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
将last_updated列添加到表中,该列由更新触发器更新
创建一个表,Locked_Widgets(或其他),它只是基表的PK和last_updated列,整个列组成了Locked_Widgets中的PK
将基本表中的非级联外键放入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.
如果有人试图更新该行,则触发器将尝试更新last_updated列,并且外键约束将导致更新失败。
#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.
很可能在数据库级别没有“优雅”的方法。但是你可以做几条路线。
- Add a "locked" bit field to each table, and when "locking" the parent cascade that value.
- In conjunction with #1 add a trigger on update and delete, if the flag is set, you can cancel the update or delete.
为每个表添加一个“锁定”位字段,并在“锁定”父级联该值时。
与#1一起在更新和删除时添加触发器,如果设置了标志,则可以取消更新或删除。
That is about the only real easy way to enforce it at the db level that I can think of.
这是我能想到的在数据库级别强制执行它的唯一真正简单的方法。
#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.
假设您的数据在SQL Server中,我会首先使用SQL Server安全性。我们通常不允许对表进行任何访问,然后通过SP控制它。 SP可以具有更复杂的逻辑来确定特定操作是否应该通过。
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.
有很多方法可以给这只猫上皮。
#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
将last_updated列添加到表中,该列由更新触发器更新
创建一个表,Locked_Widgets(或其他),它只是基表的PK和last_updated列,整个列组成了Locked_Widgets中的PK
将基本表中的非级联外键放入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.
如果有人试图更新该行,则触发器将尝试更新last_updated列,并且外键约束将导致更新失败。