Using the EventData() Function with DDL triggers in SQL Server 2005
August 29th 2008 04:47
Using the EventData() Function with DDL triggers in SQL Server 2005
Problem
In the Auditing DDL (Create, Alter, Drop) Commands in SQL Server 2005 tip, you have seen how you can track DDL activities in your server instance. But how do we store the events captured by these DDL triggers and store it in a table for reporting purposes?
Solution
In SQL Server 2005, you can get data regarding the event that initiated the DDL trigger by accessing the EventData() function. This function returns information about server or database events and is stored in a variable that uses the XML data type. What we need to do is capture the data returned by the EventData() function and store it in a database table for reporting purposes. You can store that data directly in an XML column or process it first before storing it as ordinary columns in a table. But since the returned type is XML, we need to apply a bit of XQuery against the data generated by the function. To identify the elements in the XML data returned, use Index or Search to locate the topic for the event in SQL Server Books Online. For this particular tip, we will use table-related events. The DDL_TABLE_EVENTS type describes what data can be captured for a CREATE, ALTER or DROP TABLE event.
Let's use the Northwind database for this example. We will create a table that will store the data returned by the EventData() function. We will only choose a few items from the schema generated by the ALTER_TABLE event; namely, the TSQLCommand, PostTime, LoginName, and EventType which is included in every event.
Create the event log table
USE Northwind
GO
CREATE TABLE EvtLog
(
PostTime DATETIME,
LoginName NVARCHAR(100),
EventType NVARCHAR(100),
TSQLCommand NVARCHAR(2000)
)
GO
Create the DDL trigger
CREATE 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
Code Walthrough
First, we declare a variable named @Data that will be of XML data type
Next, we assign the value returned by the EventData() function to the variable
Then, in the INSERT statement, we retrieve the element values in the @Data variable using XQuery and the value() method. We use the value() method as it takes a second parameter, represented by the value 1 as it is based on the zero index, that is the name of one of the SQL Server built-in data types. The value is returned as an instance of that type. Also, the value() method gives you better results with the formatting of the XML data
Test the trigger
Let's add a new column on the Region table in the Northwind database
USE Northwind
GO
ALTER TABLE Region
ADD newColumn SMALLDATETIME NULL
Querying the EvtLog table will give you the data returned by the EventData() function, stored in tabular format as we have already extracted the element values inside the XML data from inside our trigger definition.
select * from Northwind.dbo.evtlog
Result
postTime LoginName EventType TSQLCommand
2008-08-19 15:37:27.170 dbo Alter_Table ALTER TABLE Region ADD newColumn
SMALLDATETIME null
Problem
In the Auditing DDL (Create, Alter, Drop) Commands in SQL Server 2005 tip, you have seen how you can track DDL activities in your server instance. But how do we store the events captured by these DDL triggers and store it in a table for reporting purposes?
Solution
In SQL Server 2005, you can get data regarding the event that initiated the DDL trigger by accessing the EventData() function. This function returns information about server or database events and is stored in a variable that uses the XML data type. What we need to do is capture the data returned by the EventData() function and store it in a database table for reporting purposes. You can store that data directly in an XML column or process it first before storing it as ordinary columns in a table. But since the returned type is XML, we need to apply a bit of XQuery against the data generated by the function. To identify the elements in the XML data returned, use Index or Search to locate the topic for the event in SQL Server Books Online. For this particular tip, we will use table-related events. The DDL_TABLE_EVENTS type describes what data can be captured for a CREATE, ALTER or DROP TABLE event.
Let's use the Northwind database for this example. We will create a table that will store the data returned by the EventData() function. We will only choose a few items from the schema generated by the ALTER_TABLE event; namely, the TSQLCommand, PostTime, LoginName, and EventType which is included in every event.
Create the event log table
USE Northwind
GO
CREATE TABLE EvtLog
(
PostTime DATETIME,
LoginName NVARCHAR(100),
TSQLCommand NVARCHAR(2000)
)
GO
Create the DDL trigger
CREATE 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
Code Walthrough
First, we declare a variable named @Data that will be of XML data type
Next, we assign the value returned by the EventData() function to the variable
Then, in the INSERT statement, we retrieve the element values in the @Data variable using XQuery and the value() method. We use the value() method as it takes a second parameter, represented by the value 1 as it is based on the zero index, that is the name of one of the SQL Server built-in data types. The value is returned as an instance of that type. Also, the value() method gives you better results with the formatting of the XML data
Test the trigger
Let's add a new column on the Region table in the Northwind database
USE Northwind
GO
ALTER TABLE Region
ADD newColumn SMALLDATETIME NULL
Querying the EvtLog table will give you the data returned by the EventData() function, stored in tabular format as we have already extracted the element values inside the XML data from inside our trigger definition.
select * from Northwind.dbo.evtlog
Result
postTime LoginName EventType TSQLCommand
2008-08-19 15:37:27.170 dbo Alter_Table ALTER TABLE Region ADD newColumn
SMALLDATETIME null
| 63 |
| Vote |
Add To: 
Subscribe to this blog
Comment by TimmyH
Tech News
Can you HACK it?
Genyration