接下来定义一个DDL触发器如下
CREATE TRIGGER STOP_DDL_on_Table_and_PROC
ON DATABASE
FOR CREATE_TABLE,DROP_TABLE,
ALTER_TABLE,CREATE_PROCEDURE,
ALTER_PROCEDURE,DROP_PROCEDURE
AS
SELECT EVENTDATA().value
(''(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]'',
''nvarchar(max)'')
PRINT ''You are not allowed to CREATE,ALTER and DROP
any Tables and Procedures''
ROLLBACK;
接下来,我们尝试如下的操作:
ALTER TABLE MYTABLE ADD X INT
结果如下,出现错误提示
ALTER TABLE MYTABLE ADD X INT
(1 row(s) affected)
You are not allowed to CREATE,ALTER and DROP any Tables and Procedures
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.
再执行DROP的操作,同样触发警告
DROP TABLE MYTABLE
(1 row(s) affected)
You are not allowed to CREATE,ALTER and DROP any Tables and Procedures
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.
因为我们的触发器规定了不能使用CREATE_TABLE,DROP_TABLE,
ALTER_TABLE,CREATE_PROCEDURE,
ALTER_PROCEDURE,DROP_PROCEDURE等操作。 如果我们要关掉这个触发器,可以这样做: DISABLE TRIGGER STOP_DDL_ON_TABLE_AND_PROC
ON DATABASE 当然,我们要对整个服务器采取策略的话,也是很简单的,和上面的方法大致相同只不过将on database的参数改为on server,比如 CREATE TRIGGER STOP_DDL_on_Table_and_PROC
ON ALL SERVER
FOR CREATE_DATABASE,ALTER_DATABASE,DROP_DATABASE
AS
PRINT ''You are not allowed to CREATE,ALTER and DROP any Databases''
ROLLBACK;
无忧站长学院,建站自然无忧www.5ucms.org
上一篇:5ucms教程,SQL Server Maintenance plan 维护计划 下一篇:5ucms模版,SQL Server 2005与DB2 8.2之对比 |
|
本站声明:本网站所载文章等内容,目的在于传递更多信息,并不代表本网赞同其观点和对其真实性负责。如涉及作品内容、版权及其它问题,请在30日内与本网联系(Email:3876307#qq.com),我们将在第一时间删除内容。若原创内容转载请注明出处。 5ucms插件,SQL Server 2005中的DDL触发器的实现的关键词: |