用SQLServer2005DDL触发器对数据库进行监控

以下的文章主要向大家讲述的是SQL Server 2005DDL触发器监控数据库的相关变化，添加，删除或是修改数据库的相关对象，一旦误操作，可能会导致大麻烦，需要一个数据库治理员或开发人员对相关可能受影响的实体进行代码的重写。

创新互联专注于旌阳企业网站建设,成都响应式网站建设公司,成都商城网站开发。旌阳网站建设公司,为旌阳等地区提供建站服务。全流程按需制作网站，专业设计，全程项目跟踪，创新互联专业和态度为您提供的服务

为了在数据库结构发生变动而出现问题时，能够跟踪问题，定位问题的根源，我们可以利用SQL Server 2005DDL触发器来记录类似“用户建立表”这种变化的操作，这样可以大大减轻跟踪和定位数据库模式的变化的繁琐程度。

（1）DDL触发器介绍

DDL 触发器是一种非凡的触发器，它在响应数据定义语言 (DDL) 语句时触发。它们可以用于在数据库中执行治理任务，例如，审核以及规范数据库操作。

（2）使用 DDL 触发器，可以达到以下几种目的：

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

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

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

与标准的DML触发器一样，DDL 触发器在响应事件时执行存储过程。但与标准的DML触发器不同的是，它们并不在响应对表或视图的 UPDATE、INSERT 或 DELETE 语句时执行存储过程。它们主要在响应数据定义语言 (DDL) 语句执行存储过程。这些语句包括 CREATE、ALTER、DROP、GRANT、DENY、REVOKE 和 UPDATE STATISTICS 等语句。执行 DDL 式操作的系统存储过程也可以激发 DDL 触发器。

（3）如何使用DDL触发器

第一步，需要建立一个表，用来记录数据库范围内所有DDL操作。

下面的代码在PB2K数据库中创建一个表，用于保存所有DDL操作记录：

 
 
 
 
  
  
  
  USE AdventureWork 
  
  
  
  GO 
  
  
  
  CREATE TABLE AuditLog 
  
  
  
  (ID INT PRIMARY KEY IDENTITY(1,1), 
  
  
  
  Command NVARCHAR(1000), 
  
  
  
  PostTime NVARCHAR(24), 
  
  
  
  HostName NVARCHAR(100), 
  
  
  
  LoginName NVARCHAR(100) 
  
  
  
  ) 
  
  
  
  GO

第二步，用于保存DDL事件的表在创建好之后，还需要建立一个SQL Server 2005DDL触发器，监控PB2K数据库中DDL_DATABASE_LEVEL_EVENTS级别的所有事件：

 
 
 
 
  
  
  
  CREATE TRIGGER Audit ON DATABASE 
  
  
  
  FOR DDL_DATABASE_LEVEL_EVENTS 
  
  
  
  AS 
  
  
  
  DECLARE @data XML 
  
  
  
  DECLARE @cmd NVARCHAR(1000) 
  
  
  
  DECLARE @posttime NVARCHAR(24) 
  
  
  
  DECLARE @spid NVARCHAR(6) 
  
  
  
  DECLARE @loginname NVARCHAR(100) 
  
  
  
  DECLARE @hostname NVARCHAR(100) 
  
  
  
  SET @data=EVENTDATA() 
  
  
  
  SET @cmd=@data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','NVARCHAR(1000)') 
  
  
  
  SET@cmd=LTRIM(RTRIM(REPLACE(@cmd,'',''))) 
  
  
  
  SET@posttime=@data.value('(/EVENT_INSTANCE/PostTime)[1]','NVARCHAR(24)') 
  
  
  
  SET@spid=@data.value('(/EVENT_INSTANCE/SPID)[1]','nvarchar(6)') 
  
  
  
  SET@loginname=@data.value('(/EVENT_INSTANCE/LoginName)[1]', 
  
  
  
  'NVARCHAR(100)') 
  
  
  
  SET@hostname=HOST_NAME() 
  
  
  
  INSERT INTO dbo.AuditLog(Command,PostTime,HostName,LoginName) 
  
  
  
  VALUES(@cmd,@posttime,@hostname,@loginname) 
  
  
  
  GO

使用 EVENTDATA 函数，可以捕捉有关激发 DDL 触发器的事件的信息，并将其保存到我们的AuditLog日志表中。EVENTDATA 函数的是返回 xml 值，采用以下的命令进行解析：

 
 
 
 
  
  
  
  SET @cmd = LTRIM(RTRIM(REPLACE(@cmd,'','')))

需要LTRIM和RTRIM是所有的左边的前导空格和右边的尾随空格，而REPLACE函数是用来消除使用SSMS脚本向导时所带来的回车。

第三步，一旦建立了表和触发器，我们就可以用以下的操作，来测试看看SQL Server 2005DDL触发器是否正常运行：

 
 
 
 
  
  
  
  UPDATE STATISTICS Production.Product 
  
  
  
  GO 
  
  
  
  CREATE TABLE dbo.Test(col INT) 
  
  
  
  GO 
  
  
  
  DROP TABLE dbo.Test 
  
  
  
  GO 
  
  
  
  --View log table 
  
  
  
  SELECT * FROM dbo.AuditLog 
  
  
  
  GO

执行后，查询结果如下所示：

图1: AuditLog日志表查询结果

（4）小结

通过创建一个日志表来保存所有DDL操作以及创建数据库级别的SQL Server 2005DDL触发器，我们能够成功地捕捉我们的数据库中所有DDL级的变化，为DBA跟踪和监视任何改变提供了更强大的功能。

网站栏目：用SQLServer2005DDL触发器对数据库进行监控
网页路径：http://www.mswzjz.cn/qtweb/news2/34402.html

攀枝花网站建设、攀枝花网站运维推广公司-贝锐智能，是专注品牌与效果的网络营销公司；服务项目有等

声明：本网站发布的内容（图片、视频和文字）以用户投稿、用户转载内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。文章观点不代表本网站立场，如需处理请联系客服。电话：028-86922220；邮箱：631063699@qq.com。内容未经允许不得转载，或转载时需注明来源：贝锐智能

贝锐智能技术为您推荐以下文章