在SQL Server 2005的ddl觸發器中使用eventdata()函數

問題

在您的伺服器執行個體中，針對SQL Server 2005的提示裡，在運行資料定義語言 (Data Definition Language) (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