程序员人生 网站导航

在SQL Server 2005的ddl触发器中使用eventdata( )函数

栏目:Sqlserver时间:2014-01-14 23:00:58
问题

  在您的服务器实例中,针对SQL Server 2005的提示里,在运行数据定义语言(Create,Alter,Drop)的命令中,你就可以看到如何跟踪DDL的活动,但我们如何存储这些事件,用来捕获这些DDL触发器并且存储在表中为反馈的信息呢?

  Solution解决方案

  在SQL Server 2005中 ,您可以通过访问eventdata ( )函数,用来触发ddl事件而获取数据。 这个函数返回的是服务器或数据库事件的信息,并以XML数据类型的方式储存在一个变量中使用。 我们需要做的是捕捉eventdata ( )函数返回的数据,并将它储存在数据库的表中为反馈的信息。在把数据存储在表中的列之前,您可以把储存的数据直接放在XML的一列或者过程中。

  不过,由于返回的类型是XML ,我们需要运用的就是用XQuery所产生的数据的功能,以确定XML元素数据的返回,关于事件使用的索引或搜索相关的话题,请访问SQL Server的联机丛书。 由于这个特殊技巧,我们将使用表有关的事件。 该ddl_table_events类型说明什么数据可以捕获一个CREATE,ALTER或者DROP表的事件。

  让我们来用Northwind数据库来举一个例子。 我们将创建一个表,将用来存储eventdata ( )函数传回来的数据。 我们从ALTER_TABLE事件引起的架构中只将选择一些项目; 即在每个事件中包括TSQLCommand、PostTime、LoginName和EventType这样的字段。

  创建事件日志表

      USE NorthwindUSE Northwind
  GO
  CREATE TABLE EvtLog
  (
  PostTime DATETIME,
  LoginName NVARCHAR(100),
  EventType NVARCHAR(100),
  TSQLCommand NVARCHAR(2000)
  )
  GO GO

  创建ddl触发

      CREATE TRIGGER trPreventTblChangeCREATE TRIGGER trPreventTblChange
  ON DATABASE
  FOR ALTER_TABLE
  AS
  DECLARE @Data XML
  SET @Data = EventData()
  INSERT EvtLog (PostTime, LoginName, EventType, TSQLCommand)
  VALUES
  (GETDATE(),
  CONVERT(NVARCHAR(100), CURRENT_USER),
  @Data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
  @Data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') ) ;
  GO

  代码walthrough

  首先,我们声明一个XML数据类型的变量,命名为@Data;

  接下来,我们指派一个由EventData( )函数变量返回的值;

  然后,在INSERT语句中,使用XQuery和value()方法,我们在@Data变量中检索元素的值。我们使用@Data 的value()方法,作为两个参量,当它是零索引的时候,代表的值是1,名字是其中一个SQL服务器固定的数据类型。 值返回的是作为那个类型的实例。 并且,value()方法让您的XML数据的格式有更好的体现。

  测试触发

  在Northwind数据库中,让我们在Reion的表中添加一个新的一列。 

      USE NorthwindUSE Northwind
  GO
  ALTER TABLE Region
  ADD newColumn SMALLDATETIME NULL

  请求的EvtLog表将给您EventData ()函数返回的数据,以表格的形式存放,而我们从触发器的定义里边已经获取了在XML数据里面的元素的值。

  下一步骤

  您可以在对数据库或服务器的层次上定义ddl触发以及捕捉使用eventdata ( )函数活动的详细信息。 这种做法只是为了当ddl事件发生时,把活动的详情储存在一个日志表中。为了确保你需要验证的事件的执行,您也可以有一个中心数据库,将所有在数据库和服务器层次的事件存储起来,在如此大数额的可验证的日志操作中进行每一个事件的处理验证,这就既不高效也不有效。

  •   在您的SQL Server 2005实例中实施ddl触发器与eventdata ( )函数
  •   评估事件,您的安全团队必须经过贵公司的政策界定的基础上进行验证
  •   你可以在这里得到Northwind数据库中使用的例子。
------分隔线----------------------------
------分隔线----------------------------

最新技术推荐