Microsoft SQL Server 2012 Bible

DDL Triggers

DDL triggers are useful for auditing server-level and database changes. They can easily pinpoint which objects were changed, who changed them, and even undo unauthorized changes.

DDL triggers fire as the result of some server-level or database schema–level event — typically data definition language (DDL) code — a CREATE, ALTER, or DROP statement. Where DML triggers respond to data changes, DDL triggers respond to schema changes.

Just like DML triggers, DDL triggers can execute T-SQL code and can rollback the event. Because DDL triggers can respond to so many types of events and commands, the information about the event is passed to the trigger in XML using the EventData() function.