DDL triggers were introduced in SQL Server 2005. They execute T-SQL code when the structure of the table changes. The syntax is similar to that of DML triggers. DDL triggers are AFTER triggers that fire in response to DDL language events; they do not fire in response to system-stored procedures that perform DDL-like operations. They are fully transactional, and can be rolled back. You can run either Transact-SQL or CLR code in a DDL trigger. DDL triggers also support the EXECUTE AS clause similar to other modules.
SQL Server provides the information about the trigger event as untyped XML. It is available through a new, XML-emitting built-in function called EVENTDATA(). You can use XQuery expressions to parse the EVENTDATA() XML in order to discover event attributes like schema name, target object name, user name, as well as the entire Transact-SQL DDL statement that caused the trigger to fire in the first place. For examples, see EVENTDATA (Transact-SQL) in SQL Server Books Online.
Database-level DDL triggers fire on DDL language events at the database level and below. Examples are CREATE_TABLE, ALTER_USER, and so on. Server-level DDL triggers fire on DDL language events at the server level, for example CREATE_DATABASE, ALTER_LOGIN, etc. As an administrative convenience, you can use event groups like DDL_TABLE_EVENTS as shorthand to refer to all CREATE_TABLE, ALTER_TABLE, and DROP_TABLE events. The various DDL event groups and event types, and their associated XML EVENTDATA(), are documented in SQL Server Books Online.
Unlike DML trigger names, which are schema-scoped, DDL trigger names are database scoped or server-scoped.
Use this new catalog view to discover trigger metadata for DML triggers and database-level DDL triggers:
SELECT * FROM sys.triggers ;
If the parent_class_desc column has a value of ‘DATABASE’ then it is a DDL trigger and the name is scoped by the database itself. The body of a Transact-SQL trigger is found in the sys.sql_modules catalog view, and you can JOIN it to sys.triggers on the object_id column. The metadata about a CLR trigger is found in the sys.assembly_modules catalog view, and again, you can JOIN to sys.triggers on the object_id column.
Use this catalog view to discover metadata for server-scoped DDL triggers:
SELECT * FROM sys.server_triggers ;
The body of a Transact-SQL server-level trigger is found in the sys.server_sql_modules catalog view, and you can JOIN it to sys.server_triggers on the object_id column. The metadata about a CLR server-level trigger is found in the sys.server_assembly_modules catalog view, and again, you can JOIN to sys.server_triggers on the object_id column.
You can use DDL triggers to capture and audit DDL activity in a database. Create an audit table with an untyped XML column. Create an EXECUTE AS SELF DDL trigger for the DDL events or event groups you are interested in. The body of the DDL trigger can simply INSERT the EVENTDATA() XML into the audit table.
Another interesting use of DDL triggers is to fire on the CREATE_USER event and then add code to automate permissions management. For example, suppose you want all database users to get a GRANT EXECUTE on procedures P1, P2, and P3. The DDL trigger can extract the user name from the EVENTDATA() XML, dynamically formulate a statement like ‘GRANT EXECUTE ON P1 TO someuser’, and then EXEC() it.