Understanding DDL Triggers vs. DML Triggers
DDL triggers and DML triggers are used for different purposes.
DML triggers operate on INSERT, UPDATE, and DELETE statements, and help to enforce business rules and extend data integrity when data is modified in tables or views.
DDL triggers operate on CREATE, ALTER, DROP, and other DDL statements and stored procedures that perform DDL-like operations. They are used to perform administrative tasks and enforce business rules that affect databases. They apply to all commands of a single type across a database, or across a server.
DML triggers and DDL triggers are created, modified, and dropped by using similar Transact-SQL syntax, and share other similar behavior.
Like DML triggers, DDL triggers can run managed code packaged in an assembly that was created in the Microsoft .NET Framework and uploaded in SQL Server. For more information, see Programming CLR Triggers.
Like DML triggers, more than one DDL trigger can be created on the same Transact-SQL statement. Also, a DDL trigger and the statement that fires it are run within the same transaction. This transaction can be rolled back from within the trigger. Serious errors can cause a whole transaction to be automatically rolled back. DDL triggers that are run from a batch and explicitly include the ROLLBACK TRANSACTION statement will cancel the whole batch. For more information, see Using DML Triggers That Include COMMIT or ROLLBACK TRANSACTION.
Note
An ALTER DATABASE event that occurs inside the body of a DDL trigger cannot be rolled back.
Like DML triggers, DDL triggers can be nested. For more information, see Using Nested Triggers.
When you are designing DDL triggers, consider how they differ from DML triggers in the following ways:
DDL triggers run only after a Transact-SQL statement is completed. DDL triggers cannot be used as INSTEAD OF triggers.
DDL triggers do not create the inserted and deleted tables. The information about an event that fires a DDL trigger, and the subsequent changes caused by the trigger, is captured by using the EVENTDATA function. For more information, see Using the EVENTDATA Function.