May 29, 2011

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 –
SQL Server DDL Trigger
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()’ –

SQL Server DDL Trigger
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 –

SQL Server DDL Trigger

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


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter



 
  Feedback:

comments

0 Responses to "DDL Triggers in SQL Server 2005 and 2008"
 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions