在SQL Server 2005的ddl触发器中使用eventdata（）函数

在SQL Server 2005的ddl触发器中使用eventdata（）函数2010-08-16松晨问题

在您的服务器实例中，针对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