SQL Server 2005 中禁止删除表

SQL Server 2005 中禁止删除表

 

当设计出的数据库交付后，总会担心用户有意或无意删除的关键表，因此需要我们将它保护起来。

禁止删除表有几种方法：一、通过权限设置，二、设置关联关系，三、是通过DDL触发器。下面说一说这几种方法的优缺点，供在设计时选择，当然也可以组合起来。

一、权限设置

这种方法比较简单，在企业管理器中配置即可。不过呢，要求对数据库权限要熟些，对系统管理员的要求要大些，同时这样可能会影响客户对数据库的访问。

二、设置表的关联关系

这种方法也比较简单，在企业管理器中增加关系图即可。但是只要按着特定顺序删除也能删除了，同时，如果表是一个相对来说独立一些，强行建立关系可能会把关系搞复杂。

三、DDL触发器

这种方法要复杂一些，要进行触发器编程。DDL触发器是SQL Server 2005上新增的内容，因此要选好数据库版本。还有，就是要触发器存在并有效时才行。

下面举一个例子来说明它的使用方法：

1. USE test
2. IF EXISTS (SELECT * FROM sys.triggers WHERE parent_class = 0 AND name = 'tri_avoidDrop')
3.     DROP TRIGGER tri_avoidDrop ON DATABASE
4. GO
5. CREATE TRIGGER tri_avoidDrop 
6. ON DATABASE 
7. FOR DROP_TABLE
8. AS 
9. --指定某些表不能被drop
10.     if eventdata().value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(max)') in ('tb','ta','temp')
11.     begin
12.         PRINT '此表内有数据嘿，别jb乱删！' 
13.         ROLLBACK
14.     end
15. GO
17. --测试：
18. create table tb(id int)
19. create table tc(id int)
21. drop table tb
22. /*
23. 表内有数据嘿，别jb乱删！
24. Msg 3609, Level 16, State 2, Line 1
25. The transaction ended in the trigger. The batch has been aborted.
26. */
27. drop table tc 
28. /*
29. Command(s) completed successfully.
30. */
32. DROP TRIGGER tri_avoidDrop ON DATABASE
33. GO

例子中可以设置禁止删除哪些表，这样就可以有选择的进行控制。

 

【补充】可以单独设计一个表来管理需要进行保护的表，将表名列入其中，则该表不能被删除。

  （感谢szx1999 ）