DDL Triggers for SQL Server 2005

There are loads of features we can explore in SQL Server 2005. Here is one such feature that I personally would call it as the DBA's requirement. Here in this article we will take a snapshot to what DDL triggers are and how it can be extended to our needs. DDL Triggers are new in SQL Server 2005. Fundamentally SQL Server 2005 allows us to existing set of available triggers to the next level. In these trigger we can execute a trigger for all available DDL statements in the system. Let's take a quick snapshot of using the same:

-- Creating the database trigger for execution

CREATE TRIGGER DDLBlockTrg

ON DATABASE

FOR DROP_TABLE, ALTER_TABLE

AS

DECLARE @raisedEventData XML

-- Capture the event data that is created

SET @raisedEventData = eventdata()

-- Extract information in a tabular format

SELECT

@raisedEventData.query ('data(/EVENT_INSTANCE/PostTime)') AS [Event Time],

@raisedEventData.query ('data(/EVENT_INSTANCE/EventType)') AS [Event Type],

@raisedEventData.query ('data(/EVENT_INSTANCE/ServerName)') AS [Server Name],

@raisedEventData.query ('data(/EVENT_INSTANCE/TSQLCommand/CommandText)') AS [Command Text]

-- Print an error message and then rollback the command

PRINT 'Alter Table statements are not allowed in this database'

ROLLBACK

GO

Check the keywords that get added to the normal trigger syntax. Primarily the trigger captures the DDL operations at the database objects hence we add "ON DATABASE" and follow the same with the DDL operation name. Since in the above example we would like to monitor the Alter and the Drop statements on a table, once we perform any of the above operation we get an error:

Alter Table statements are not allowed in this database

Msg 3609, Level 16, State 2, Line 6

Transaction ended in trigger. Batch has been aborted.

Now that we created the trigger, we might want to look at the trigger related system tables and there are a couple of tables that we might be interested to ping:

-- Shows the triggers created

Select * from sys.triggers

-- Shows the triggers created for which DDL/DML operation

Select * from sys.trigger_events

Here are the DDL Trigger events that you would be able to catch in the current Version. This might not be exhaustive in nature but gives you an idea to what to expect:

Create_Table	Drop_Assembly	Create_Service
Alter_Table	Create_Type	Alter_Service
Drop_Table	Drop_Type	Drop_Service
Create_View	Create_User	Create_Route
Alter_View	Alter_User	Alter_Route
Drop_View	Drop_User	Drop_Route
Create_Synonym	Create_Role	Create_Binding
Drop_Synonym	Alter_Role	Alter_Binding
Create_Function	Drop_Role	Drop_Binding
Alter_Function	Create_AppRole	Grant_Server
Drop_Function	Alter_AppRole	Deny_Server
Create_Procedure	Drop_AppRole	Revoke_Server
Alter_Procedure	Create_Schema	Grant_Database
Drop_Procedure	Alter_Schema	Deny_Database
Create_Trigger	Drop_Schema	Revoke_Database
Alter_Trigger	Create_Login	Create_Secexpr
Drop_Trigger	Alter_Login	Drop_Secexpr
Create_Event_Notification	Drop_Login	Create_XmlSchema
Drop_Event_Notification	Create_Endpoint	Alter_XmlSchema
Create_Index	Alter_Endpoint	Drop_XmlSchema
Alter_Index	Drop_Endpoint	Create_Cert
Drop_Index	Create_MsgType	Alter_Cert
Create_Stats	Alter_MsgType	Drop_Cert
Update_Stats	Drop_MsgType	Create_Partition_Function
Drop_Stats	Create_Contract	Alter_Partition_Function
Create_Database	Alter_Contract	Drop_Partition_Function
Alter_Database	Drop_Contract	Create_Partition_Scheme
Drop_Database	Create_Queue	Alter_Partition_Scheme
Create_Assembly	Alter_Queue	Drop_Partition_Scheme
Alter_Assembly	Drop_Queue

PS: This list is based on the Beta releases and might be subjected to change in the final RTM

Surely mind blowing number of events to capture. Something I missed in the above explanation is the use of this special function eventdata() that was used in our trigger code. EVENTDATA is called when an event notification fires, and the results are returned to the specified service broker. EVENTDATA returns a value of type xml. And it contains the server, database, type of operation, command for the operation, instance information, connection ID and so on. In the above example the CommandText contains the DDL statement that fired the trigger. Depending on the type of event fired the XML information varies. The complete XML structure is described in the documentation.

Conclusion

This article did give you a sneak preview to how to implement and use DDL triggers in SQL Server 2005. This feature is not limited but is only limited to the imagination of the implementer. You can interface with Service Broker from the triggers and raise an event to notify a DBA automatically when an illegal operation is performed on mission critical data. So DDL Trigger surely opens greater avenues and control for administration.