数据库结构监控2 DDL 触发器介绍

这里简单介绍一下DDL 触发器。

 

 是为了响应各种数据定义语言 (DDL) 事件而激发。这些事件主要与以关键字 CREATE、ALTER 和 DROP 开头的 Transact-SQL 语句对应。执行 DDL 式操作的系统存储过程也可以激发 DDL 触发器。

测试您的 DDL 触发器以确定它们是否响应运行的系统存储过程。例如，CREATE TYPE 语句和 sp_addtype 存储过程都将激发针对 CREATE_TYPE 事件创建的 DDL 触发器。.

仅在运行触发 DDL 触发器的 DDL 语句后，DDL 触发器才会激发。

 

如果要执行以下操作，请使用 DDL 触发器：

要防止对数据库架构进行某些更改。

希望数据库中发生某种情况以响应数据库架构中的更改。

要记录数据库架构中的更改或事件。

注意

数据库和服务器实例中存在的 DML 和 DDL 触发器，方法是查询 sys.triggers 和 sys.server_triggers 目录视图。下面的查询将返回当前数据库中的所有 DML 触发器和数据库级别的 DDL 触发器，以及服务器实例中所有服务器级别的 DDL 触发器：

SELECT type, name, parent_class_desc FROM sys.triggers

UNION

SELECT type, name, parent_class_desc FROM sys.server_triggers ;

 

 

如何创建DDL触发器： 

  Trigger on a CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE STATISTICS statement (DDL Trigger)

CREATE   TRIGGER  trigger_name 
ON  {  ALL  SERVER  |   DATABASE  } 
[  WITH <ddl_trigger_option> [ ,...n  ]  ]
{  FOR   |  AFTER } { event_type  |  event_group }  [  ,...n  ]
AS  { sql_statement   [  ;  ]   [  ,...n  ]   |  EXTERNAL NAME  <  method specifier  >    [  ;  ]  }

< ddl_trigger_option >  :: =
     [  ENCRYPTION  ]
     [  EXECUTE AS Clause  ]

< method_specifier >  :: =
    assembly_name.class_name.method_name

Trigger   on  a LOGON event (Logon  Trigger )
CREATE   TRIGGER  trigger_name 
ON   ALL  SERVER 
[  WITH <logon_trigger_option> [ ,...n  ]  ]
{  FOR   |  AFTER } LOGON  
AS  { sql_statement   [  ;  ]   [  ,...n  ]   |  EXTERNAL NAME  <  method specifier  >    [  ;  ]  }
< logon_trigger_option >  :: =
     [  ENCRYPTION  ]
     [  EXECUTE AS Clause  ]

< method_specifier >  :: =
    assembly_name.class_name.method_name  

 

 这里主要就是注意： ON ALL SERVER 或 ON DATABASE 以及  FOR event_type | event_group 

  ON ALL SERVER ：是服务器级的（就是实例）

  ON DATABASE：是数据库级的

 哪些事件能触发这个触发器就要看

 FOR event_type | event_group 

 

这里2005与2008 有较大的区别。 

 

sql 2005 的事件组 

 

 

 

sql 2008 的事情组 

 

 

 这两张图在结构层次上面，和范围上面可以看的出来2008 比2005 改进了不少。

 

 首先看出来的是 2008 有个总的事件组，2005没有。

 然后我们测试一下服务器事件组。

 

CREATE   TRIGGER  ddl_trig_login 
ON   ALL  SERVER 
FOR  ddl_server_level_events

AS  
      print   ' a '
GO

 

 

 消息 1082，级别 15，状态 1，过程 ddl_trig_login，第 6 行

"DROP_SERVER_ROLE_MEMBER" 不支持同步触发器注册。

上面这个执行就不正常了 ，原因提示的非常清楚。解决办法，我还是找不到。这方面资料网上也不多。

 

我们换了个事件组再测试 

CREATE   TRIGGER  ddl_trig_login 
ON   ALL  SERVER 
FOR  ddl_login_events

AS  
      print   ' a '
GO

 

 命令已成功完成。

 

 就换了个事件组执行成功了。

 

 假设我要监控服务器的所有事件并记录下操作内容，那么我只能一个事件一个事件去穷举。

下面我们也作个测试：

  事件与事件组配合写把SQL DDL 服务器级的全部都写上了。大家可以对照一下上面那个图。

CREATE   TRIGGER  ddl_trig_login 
ON   ALL  SERVER 
FOR  ddl_login_events,ddl_gdr_server_events,ddl_authorization_server_events ,
create_database,alter_database,drop_database,ddl_endpoint_events

AS  
      print   ' a '
GO

 

  命令已成功完成。

执行成功了，当然我还做了个测试，把所有的事件写上。也成功了。

那我就搞不清楚为什么用服务器级的事件组会提示 "DROP_SERVER_ROLE_MEMBER" 不支持同步触发器注册。

这个drop_server_role_member 确实不在指定的事件里面。但是在那张图上又没有体现出来它在哪个位置。

这不是扯蛋吗？ 我也不去多想了。帮助文档写的不够好。 

 

 还有个范围：

 

CREATE   TRIGGER  ddl_trig_login 
ON   ALL  SERVER 
FOR   ddl_database_level_events
AS  
      print   ' a '
GO

 

SQL 2005上面的执行提示： 

消息 1098，级别 15，状态 1，过程 ddl_trig_login，第 5 行

指定的事件类型对指定的目标对象无效。

 SQL 2008上面执行成功！

 

DDL 触发器与DML触发器不同，DDL 没有类似与 INSERT OF 的功能。 

 

 

 

转载于:https://www.cnblogs.com/hsj2010/archive/2010/10/19/1848679.html