DDL Triggers in SQL Server 2005


Home About Us Sign Guest Book Contact Us Subscribe RSS

SQL

Articles

.NET

Blogs

Newsletter

Click on the image to subscribe to our Monthly Newsletter.

Click here for Archives

Expert Chat

No Chats Available

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.

	Comment about this article
Free Hit Counters	Copyright © 2000-2003 ExtremeExperts.com, Terms of Use Best viewed on 1024x768 using Microsoft Internet Explorer 5.5 and later. Hosted on Windows Server 2003/.NET Framework 1.1.