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.
There are dozens of events that can potentially ...