在某些情况下需要把SQLServer的表设为只读,下面举出几种方法:
一般情况下会有几种情况需要你把数据库设为只读:
1、Insert,Update,Delete 触发器
2、Check 约束 和 Delete 触发器
3、设置数据库为只读
4、把表放到只读文件组中
5、拒绝对象级别权限
6、创建视图
在开始之前,先创建一个数据库及表作为示例:
create database MyDB create table tblEvents ( id int, logEvent varchar(1000) ) insert into tblEvents values (1, 'Password Changed'), (2, 'User Dropped'), (3, 'Finance Data Changed') |
nsert/Update/Delete触发器:
请注意这里使用的是INSTEADOF trigger,因为如果你使用了AFTER trigger,会在执行DELETE, UPDATE和INSERT语句时请求锁,会对写事务日志和回滚操作造成性能上的影响
CREATE TRIGGER trReadOnly_tblEvents ON tblEvents INSTEAD OF INSERT, UPDATE, DELETE AS BEGIN RAISERROR( 'tblEvents table is read only.', 16, 1 ) ROLLBACK TRANSACTION END |
当用户执行insert/update/delete时,将提示以下错误:
Msg 50000, Level 16, State 1, Procedure trReadOnly_tblEvents, Line 7tblEvents table is read only.Msg 3609, Level 16, State 1, Line 1The transaction ended in the trigger. The batch has been aborted.
使用 Check 约束和Delete 触发器:
现在先在表中添加一个check 约束“1=0”,意味着总是失败。它禁止你在任何行执行INSERT或者Delete操作。
首先,先禁用在上一步创建的触发器:disable trigger trReadOnly_tblEvents on tblevents然后,添加约束:ALTER TABLE tblEvents WITH NOCHECK ADD CONSTRAINT chk_read_only_tblEvent CHECK( 1 = 0 )执行以后,无论你执行任何一个INSERT/UPDATE语句,都将提示以下错误信息:
Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the CHECKconstraint "chk_read_only_tblEvent". The conflict occurred indatabase "MyDB", table "dbo.tblEvents".
The statement has been terminated.
但是,该约束不会对DELETE操作造成影响,为此,需要再创建一个DDL触发器:
CREATE TRIGGER trReadOnlyDel_tblEvents ON tblEvents INSTEAD OF DELETE AS BEGIN RAISERROR( 'tblEvents table is read only.', 16, 1 ) ROLLBACK TRANSACTION END |