February 18, 2019
Hot Topics:

Using DDL Triggers to Manage SQL Server 2005

  • September 28, 2005
  • By Jeffrey Juday
  • Send Email »
  • More Articles »

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.

Page 2 of 2

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Thanks for your registration, follow us on our social networks to keep up-to-date