Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

Using the EventData() Function with DDL triggers in SQL Server 2005

MSSQLTips author Edwin Sarmiento By:   |   Read Comments (5)   |   Related Tips: More > Triggers

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 (PostTimeLoginNameEventTypeTSQLCommand)  
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.

Next Steps

You can define DDL triggers as well as capture event details using the EventData() function on the database or server level. This approach is just to audit DDL activities and store the event details in a log table. You can have a central database that will store all the events done on both the database and server level. It is not efficient nor effective to audit every event due to the immense amount of audit record logging processes so make sure that you plan what events you need to audit.

  • Implement DDL triggers with EventData() function in your SQL Server 2005 instances
  • Evaluate events that need to be audited based on your corporate policies as defined by your security teams
  • You can get the Northwind database used in the sample here.


Last Update: 8/27/2008


About the author
MSSQLTips author Edwin Sarmiento
Edwin Sarmiento works as a SQL Server DBA for The Pythian Group in Ottawa and is a SQL Server MVP.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Thursday, October 16, 2014 - 3:10:32 AM - Atiqrahman Read The Tip

Can we get the connection string through the Eventdata function?

 

Thanks,

Atiqrahman


Wednesday, February 13, 2013 - 6:35:22 AM - Jaya Read The Tip

 

I am trying to design a Database-level DDL trigger that automatically creates table with same structure and with different name on same/different database when a CREATE Table is triggered. I similarly require to handle modification of table stuctures to reflect on other table.


Friday, January 11, 2013 - 6:13:38 PM - bass_player Read The Tip

I haven't tested this yet but it seems that it won't work. The reason being is that there are certain transactions that cannot be rolled back, such as creating a database. Therefore, you won't b able to include it in your trigger definition. I've done a similar thing before that automatically runs a backup of a newly created database and that also didn't work.


Friday, January 11, 2013 - 4:22:05 PM - Joe Read The Tip

I am trying to design a server-level DDL trigger that automatically creates objects on a newly created database (schema, functions, etc.) when a CREATE DATABASE is triggered.  However, it does not appear that I am able to switch context to the newly created database to create these objects.  Is there something I'm missing?  Is this even possible?

 

IF EXISTS (SELECT * FROM sys.server_triggers
    WHERE name = 'ddl_trig_database')
DROP TRIGGER ddl_trig_database
ON ALL SERVER;
GO

CREATE TRIGGER ddl_trig_database
ON ALL SERVER
AFTER CREATE_DATABASE
AS
    declare @curdb as varchar(256);
    set @curdb = DB_NAME(); -- this is typically set to 'master'
   
    declare @newdb as varchar(256);
    set @newdb = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(max)');

    EXEC ('use ' + @newdb);    -- doesn't work

    declare @ex_script as varchar(max);
    set @ex_script= 'CREATE SCHEMA [xyz] AUTHORIZATION [dbo];'
   
    EXEC (@ex_script);      -- this creates the schema on 'master' instead of the new db

    EXEC ('use ' + @curdb);
GO


Tuesday, April 24, 2012 - 2:40:10 AM - Brando Mortiz Read The Tip

TSQLCommand data from EventData() is shown in 1 line only. This would be troublesome to read especially for stored procedures composed of many lines. How would you display this in paragraph form? with the line feeds and carriage returns?




 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.