Microsoft SQL Server 2012 Bible

Managing DDL Triggers

DDL triggers are easy to manage using normal DDL. The most significant factor when developing a DDL trigger is the scope of the trigger — deciding which server- or database-level events will fire a trigger.

Creating and Altering DDL Triggers

DDL triggers are created or altered using syntax similar to working with DML triggers. The location of the trigger, specified by the ON clause, is either ALL SERVER or DATABASE. The following code creates a database-level DDL trigger:

CREATE TRIGGER SchemaAudit
ON DATABASE
FOR DDL_Database_Level
AS
code

Server-level events are a superset of database-level events. They include all database level events. The next example shows a server-level DDL trigger:

CREATE TRIGGER SchemaAudit
ON ALL SERVER
FOR DDL_Server_Level
WITH Options
AS
code

Using Management Studio, database triggers are listed under the database's Programmability node in Object Explorer. Server triggers are listed under Server Objects in Object Explorer. Database triggers can be scripted using Object Explorer but not modified as easily as other programmability objects such as stored procedures. The context menu for DDL triggers does not offer the modify or script to alter options that a stored procedure's context menu includes.

To list the database DDL triggers using code, query the sys.triggers and sys.events catalog views. Server triggers are found at sys.server_triggers and sys.server_trigger_events.

Trigger Scope

There are dozens of events that can potentially ...