Using DDL Triggers to Manage SQL Server 2005

SQL Server 2005 has extended the trigger functionality you normally use with Data Manipulation Language (DML) commands such as INSERT, UPDATE, and DELETE to incorporate Data Definition Language (DDL) commands like CREATE DATABASE, DROP TABLE, and ALTER TABLE. The new technology, called DDL triggers, comes in two flavors: database-level triggers that respond to changes to the database and server-level triggers that respond to changes on the server.

This article uses the sample AdventureWorks database and the SQL Server 2005 CTP to introduce DDL triggers and provide scenarios for using them.

Introducing DDL Triggers

Before delving into DDL triggers, learning what DDL triggers have in common with DML triggers may be helpful. First, DDL triggers are created, changed, and removed by using statements such as CREATE TRIGGER, ALTER TRIGGER, and DROP TRIGGER. The following is a typical DDL CREATE TRIGGER statement:

CREATE TRIGGER ddl_trig_loginAW
ON ALL SERVER
FOR DDL_LOGIN_EVENTS
AS
   PRINT 'Added trigger event to DDLServerTriggerData'
   INSERT INTO [AdventureWorks].[dbo].[dbo.DDLServerTriggerData]
               (DDLServerEvent) VALUES ( EVENTDATA())

Also, like DML triggers, DDL triggers run in the same transaction as the DML statement. So for instance, when a DDL TSQL statement has completed, you can rollback a transaction whenever appropriate.

Finally, both DDL and DML triggers can use the new CLR functionality in SQL Server 2005 for running a managed code assembly uploaded to SQL Server.

Unlike DML triggers, DDL triggers respond to completely different events. As previously stated, DDL triggers respond to changes to a database or a server. Each time DDL statements make changes using commands such as CREATE TABLE, an event is fired. DDL triggers can subscribe to those events and execute other TSQL instructions in response to the events.

Some other differences between DDL and DML triggers include the following:

• DDL triggers do not support the INSTEAD of functionality in the CREATE TRIGGER statement.
• DDL triggers are missing the inserted and deleted tables common to DML triggers.

Now that you're familiar with what DDL triggers do, you can set up a DDL trigger of your own.

Designing a DDL Trigger

When you define a DDL trigger, you must decide on the scope of your trigger. Scope determines whether the trigger executes at the database or the server level. (A forthcoming section discusses scope in more detail later in the article.)

After determining scope, you must decide whether the DDL trigger responds to a single DDL statement or a group of related statements. For example, the DDL trigger can respond to the DROP TABLE statement or all table operations (DROP, CREATE, ALTER). Typically, single events are named by using the TSQL statement and separating each word in the statement with an underscore (for example, CREATE_DATABASE). (Refer to the SQL Server 2005 Books Online for a complete list of events a DDL trigger can subscribe to.)

Once you've determined scope and events, you are ready to code the proper response. You will find the EVENTDATA() TSQL function most helpful for coding your response.

The EVENTDATA() Function

The EVENTDATA() function returns XML data with information such as event time, System Process ID (SPID), and type of event firing the trigger. A DDL trigger uses the EVENTDATA() function to determine how it must respond.

To retrieve information from XML data, you must use XQuery. A complete discussion of XQuery and the new XML Datatype is beyond the scope of this article. You can find an introduction of the XML Datatype on Developer.com at "The Fundamentals of the SQL Server 2005 XML 3Datatype".

The following is a sample of XML data returned by the EVENTDATA() function:

<EVENT_INSTANCE>
   <EventType>CREATE_TABLE</EventType>
   <PostTime>2005-07-30T10:48:52.537</PostTime>
   <SPID>55</SPID>
   <ServerName>PSE-TEST-JON1</ServerName>
   <LoginName>PSE-TEST-JON1\Administrator</LoginName>
   <UserName>dbo</UserName>
   <DatabaseName>AdventureWorks</DatabaseName>
   <SchemaName>Person</SchemaName>
   <ObjectName>Address</ObjectName>
   <ObjectType>TABLE</ObjectType>
   <TSQLCommand>
      <SetOptions ANSI_NULLS="ON"
                  ANSI_NULL_DEFAULT="ON"
                  ANSI_PADDING="ON"
                  QUOTED_IDENTIFIER="ON"
                  ENCRYPTED="FALSE" />
      <CommandText>CREATE TABLE [Person].[Address](
                                [AddressID] [int]
                                IDENTITY (1, 1)
                                NOT FOR REPLICATION NOT NULL,
      [AddressLine1] [nvarchar](60) NOT NULL,
      [AddressLine2] [nvarchar](60) NULL,
      [City] [nvarchar](30) NOT NULL,
      [StateProvinceID] [int] NOT NULL,
      [PostalCode] [nvarchar](15) NOT NULL,
      [rowguid] uniqueidentifier ROWGUIDCOL NOT NULL CONSTRAINT
         [DF_Address_rowguid] DEFAULT (NEWID()),
      [ModifiedDate] [datetime] NOT NULL CONSTRAINT
         [DF_Address_ModifiedDate] DEFAULT (GETDATE())
      ) ON [PRIMARY];</CommandText>
   </TSQLCommand>
</EVENT_INSTANCE>

As you can see, there are other pieces of information that depend on the DDL statement firing the trigger. (See the SQL Server 2005 Books Online for a complete list of XML Schemas.) The following sample code illustrates how you will access the XML returned by the EVENTDATA() function:

DECLARE @data XML;
DECLARE @schema sysname;
DECLARE @object sysname;
DECLARE @eventType sysname;
SET @data = EVENTDATA();
SET @eventType = @data.value('(/EVENT_INSTANCE/EventType)[1]',
                             'sysname');
SET @schema    = @data.value('(/EVENT_INSTANCE/SchemaName)[1]',
                             'sysname');
SET @object    = @data.value('(/EVENT_INSTANCE/ObjectName)[1]',
                             'sysname')

One important point concerning XQuery: If you're going to retrieve single values from the EVENTDATA() XML, use the "value" statement rather than the "query" statement. You will have better results with the formatting of the XML data.

DDL triggers can fire for changes in either the database or server. Now, the discussion turns to trigger scope in more detail.

Implementing a Database-Scoped DDL Trigger

Database-scoped DDL triggers respond to events that modify the database schema. In the CREATE TRIGGER statement, the ON DATABASE directive scopes the trigger for a database.

Database-scoped DDL triggers are stored within the database and can fire on an event, except those related to temporary tables. Figure 1 shows the location of the AdventureWorks DDL trigger sample.

Click here for a larger image.

Figure 1. Location of the AdventureWorks DDL Trigger

DDL triggers scoped to the database can fire for everything but statements relating to temporary tables.

Implementing a Server-Scoped DDL Trigger

Server-scoped DDL triggers respond to a limited set of server changes. (For a complete list, refer to the SQL Server 2005 Books Online.) In the CREATE TRIGGER statement, the ON ALL SERVER directive scopes the DDL trigger to the server.

DDL triggers are stored as objects in the master database. Figure 2 shows where you locate DDL triggers scoped to the server.

Click here for a larger image.

Figure 2. Locate DDL triggers Scoped to the Server

Events are confined to database changes (CREATE, ALTER, DROP) and some security-level events such as CREATE LOGIN, ALTER LOGIN, or DROP LOGIN.

DDL triggers are not the only option for responding to database or server events. Event notifications also are an option.

Event Notifications—The Other Logging Option

DDL triggers are not the only mechanisms for logging DDL events. Event notifications are another option. A full discussion of event notifications is beyond the scope of this article. Instead, it briefly discusses the major differences between event notifications and DDL triggers.

Event notifications are similar to DDL triggers. Unlike a DDL trigger, an event notification is asynchronous and requires the SQL Server 2005 Service Broker. The SQL Server Service Broker processing the event notifications need not be on the same server issuing the event notification.

Event notifications are not confined to DDL events. They also can respond to some SQL trace events.

Like DDL triggers, event notifications utilize similarly formatted XML, but unlike DDL triggers, they do not use the EVENTDATA() function.

The most striking difference between event notifications and DDL triggers is that event notifications do not run in the scope of the transaction. Therefore, you can't rollback the transaction from within an event notification the way you can rollback a transaction within a DDL trigger.

Scenarios for Using a DDL Trigger

You can prevent or log changes to your database. As a DBA or database developer, it may be important to know if and when something in your database has changed. In some cases, you may even want to prevent changes by any user altogether.

You may have naming conventions in your database. DDL triggers can enforce them by preventing improperly named tables, views, stored procedures, and even table columns.

You may want to perform other actions in response to a DDL event. You could, for example, create a record in the server's event log or execute CLR code you've installed on your SQL Server.

Respond to Database and Server Changes

As a DBA or database developer, it may be important to respond to changes in a SQL Server 2005 database or server. DDL triggers allow you to respond to and prevent changes altogether.

Download the Code

To download the accompanying source code for the examples, click here.

About the Author

Jeffrey Juday is a software developer with Crowe Chizek in South Bend, Indiana. He has been developing software with Microsoft tools for more than 12 years in a variety of industries. Jeff currently builds solutions using BizTalk 2004, ASP.NET, SharePoint, and SQL Server 2000. You can reach Jeff at jjuday@crowechizek.com.