DDL Triggers in SQL Server 2005 and 2008

In this post, we will see how to implement DDL triggers in SQL Server 2005 and 2008. 

In SQL Server 2005, Microsoft introduced DDL triggers that fire in response to Data Definition Language (DDL) statements and can be implemented for various operations such as auditing in SQL Server.

DDL triggers can be fired for the following statements –

1. CREATE.
2. ALTER.
3. DROP.
4. Other DDL statements.

For a complete list for DDL events, click on the following link – http://technet.microsoft.com/en-us/library/bb522542.aspx

Now let’s start implementing some DDL triggers by using T-SQL. Open SQL Server Management Studio and connect to the Northwind database –

When you execute the drop table command, you will encounter the user defined message shown above.

Let’s drop the trigger for the time being –

DROP TRIGGER NoDropTables ON DATABASE

Now let’s write a trigger which will disallow all the DDL operations, as shown below –

CREATE TRIGGER NoDDLEventsOnDatabase
ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN
    PRINT 'You are not allowed to Perform DDL Commands on this Dattabase'
    ROLLBACK
END

Try and perform a CREATE, ALTER or DROP statement. The statement will be rollback.

We will now audit the database. For auditing database level trigger, we have a very special function called
‘EVENTDATA()’. Let’s alter the trigger we created above to see the audit data –

ALTER TRIGGER NoDDLEventsOnDatabase
ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN
    DECLARE @AuditData XML
    SET @AuditData=EVENTDATA()
END

Try to drop the table and see the data returned by ‘EVENTDATA()’ –


The above XML is returned by the ‘EVENTDATA()’ function. Now let’s insert this data in a table so that we can design a report with all this data, at the end of the day –

Design the table first as shown below –



Now let’s alter a trigger as shown below –

ALTER TRIGGER NoDDLEventsOnDatabase
ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN
    DECLARE @AuditData XML
    SET @AuditData=EVENTDATA()
    INSERT INTO AuditDLLStatements VALUES
    (GETDATE(),CONVERT(NVARCHAR(50),@AuditData.query('data(//ServerName)')),CONVERT(NVARCHAR(50),@AuditData.query('data(//LoginName)')),CONVERT(NVARCHAR(50),@AuditData.query('data(//EventType)')),CONVERT(NVARCHAR(1000),@AuditData.query('data(//TSQLCommand)')))
END

and that’s it! You may also want to read Display DDL Triggers in your SQL Server Database