sql server 2005中的DDL触发器

SQL SERVER 2005中，新增加了许多新的特性，其中的DDL触发器是个不错的选择，根据资料初步学习如下，现整理之：

    

在sql server 2000中，只能为针对表发出的 DML 语句（INSERT、UPDATE 和 DELETE）定义 AFTER 触发器。SQL Server 2005 可以就整个服务器或数据库的某个范围为 DDL 事件定义触发器。可以为单个 DDL 语句（例如，CREATE_TABLE）或者为一组语句（例如，DDL_DATABASE_LEVEL_EVENTS）定义 DDL 触发器。在该触发器内部，您可以通过访问 eventdata() 函数获得与激发该触发器的事件有关的数据。该函数返回有关事件的 XML 数据。每个事件的架构都继承了 Server Events 基础架构。

    

比如，在SQL SERVER 2005中，建立一个叫DDLTrTest 的数据库，并且建立一个叫mytable的表

    

和Usp_Querymytable 的存储过程，如下所示

  DROP   DATABASE   [ DDLTRTEST ]
GO
CREATE   DATABASE DDLTRTEST
GO
USE   [ DDLTRTEST ]
GO
IFEXISTS ( SELECT   *   FROM SYS.OBJECTS
WHERE   OBJECT_ID   =   OBJECT_ID (N ' [DBO].[MYTABLE] ' )
AND TYPE IN (N ' U ' ))
DROP   TABLE   [ DBO ] . [ MYTABLE ]
GO
CREATE   TABLE MYTABLE(ID INT , NAME VARCHAR ( 100 ))
GO
INSERT   INTO MYTABLE SELECT   1 , ' A '
INSERT   INTO MYTABLE SELECT   2 , ' B '
INSERT   INTO MYTABLE SELECT   3 , ' C '
INSERT   INTO MYTABLE SELECT   4 , ' D '
INSERT   INTO MYTABLE SELECT   5 , ' E '
INSERT   INTO MYTABLE SELECT   6 , ' F '
GO
USE   [ DDLTrTest ]
GO
IFEXISTS ( SELECT   *   FROM sys.objects WHERE   object_id   =
OBJECT_ID (N ' [dbo].[usp_querymytable] ' )
AND type in (N ' P ' , N ' PC ' ))
DROP   PROCEDURE   [ dbo ] . [ usp_querymytable ]
GO
CREATE   PROC USP_QUERYMYTABLE
AS
SELECT   *   FROM MYTABLE
GO  

    

接下来定义一个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 ;

挥辉 2007-09-04 23:36 发表评论