Using the EventData () function in a DDL trigger in SQL Server 2005

Problem

In your server instance, for SQL Server 2005 prompts, you can see how to track DDL activity in commands running Data definition language (create,alter,drop), but how do we store these events, Information to capture these DDL triggers and store in the table for feedback?

Solution Solutions

In SQL Server 2005, you can get data by accessing the EVENTDATA () function to trigger a DDL event. This function returns information about the server or database event and is stored in a variable in the form of an XML data type. What we need to do is to capture the data returned by the EVENTDATA () function and store it in the database table for feedback information. Before you store data in a column in a table, you can place the stored data directly in a column or process of XML.

However, since the returned type is XML, we need to use the functionality of the data produced by XQuery to determine the return of the XML element data, the index to use for the event, or the topic of the search, please visit the SQL Server Books Online. Due to this particular technique, we will use tables related to events. The ddl_table_events type describes what data can capture an event in a create,alter or drop table.

Let's use the Northwind database to give an example. We will create a table that will be used to store the data returned by the EVENTDATA () function. We will only select some items from the architecture caused by the alter_table event; That is, fields such as Tsqlcommand, Posttime, LoginName, and EventType are included in each event.

Create an Event log table

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

Creating a DDL Trigger

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