A DBA's Dream - SQL 2005 DDL Triggers

I’ve never put a trigger into production. I’m not saying this is good or bad, it just hasn’t happened …. yet.

With all these new features in SQL 2005 it’s easy to overlook the new capability to use DDL triggers. I think they will become a DBA’s friend long before CREATE ASSEMBLY and stored procedures in managed code ever will.

DDL triggers fire when data definition language events happen. For instance, you can block DROP TABLE and ALTER TABLE statements in a database with the following trigger.

CREATE TRIGGER AuditTableDDL
ON DATABASE 
FOR DROP_TABLE, ALTER_TABLE 
AS 
   PRINT 'No DROP or ALTER for you!' 
   PRINT CONVERT (nvarchar (1000), EVENTDATA())
   ROLLBACK;

In SQL 2000, the only way to prevent an accidental table drop was by using CREATE VIEW to touch a table and add the WITH SCHEMABINDING clause. DDL triggers are more explicit about this, and give you auditing capability easily with EVENTDATA().

The EVENTDATA() function is what really makes DDL interesting. If you need to audit DDL activity, the XML return value will contain all of the pertinent information for you. For example, if I have table Foo, and try DROP TABLE Foo with the previous trigger in place, I’ll get the following response (with some formatting applied):

No DROP or ALTER for you!
 
<EVENT_INSTANCE>
  <EventType>DROP_TABLE</EventType>
  <PostTime>2004-08-22T22:54:37.377</PostTime>
  <SPID>55</SPID>
  <ServerName>SQL2005B2</ServerName>
  <LoginName>SQL2005B2\bitmask</LoginName>
  <UserName>SQL2005B2\bitmask</UserName>
  <DatabaseName>AdventureWorks</DatabaseName>
  <SchemaName>dbo</SchemaName>
  <ObjectName>Foo</ObjectName>
  <ObjectType>TABLE</ObjectType>
  <TSQLCommand>
    <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" 
                ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON"
                ENCRYPTED="FALSE"
    />
    <CommandText>DROP TABLE Foo</CommandText>
  </TSQLCommand>
</EVENT_INSTANCE>
 
Msg 3609, Level 16, State 2, Line 1
Transaction ended in trigger. Batch has been aborted.

SQL 2005 also has predefined event groups to make writing DDL triggers easier. In the following trigger, DDL_TABLE_EVENTS will catch CREATE TABLE, DROP TABLE, and ALTER TABLE:

CREATE TRIGGER AuditTableDDL
ON DATABASE 
FOR DDL_TABLE_EVENTS
AS   
   PRINT CONVERT (nvarchar (1000), EVENTDATA())
   ROLLBACK;

Likewise DDL_INDEX_EVENTS will fire on CREATE, ALTER, or DROP INDEX. These groups roll up into larger groups. DDL_TABLE_VIEW_EVENTS will fire on all table, view, index, or statistics DDL.

The above triggers operate at database scope, and only react to events in the current database. You can also apply triggers at a server scope to fire on CREATE, DROP, ALTER LOGIN, for instance.

And of course you could write the trigger in C# or VB.NET, but let’s not get ahead of ourselves just yet…