Using DDL Triggers to Manage SQL Server 2005, Page 2
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.
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.
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 NotificationsThe 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 firstname.lastname@example.org.