Understanding DDL Triggers
DDL triggers, like regular triggers, fire stored procedures in response to an event. However, unlike DML triggers, they do not fire in response to UPDATE, INSERT, or DELETE statements on a table or view. Instead, they fire in response to a variety of Data Definition Language (DDL) events. These events primarily correspond to Transact-SQL statements that start with the keywords CREATE, ALTER, and DROP. Certain system stored procedures that perform DDL-like operations can also fire DDL triggers.
Important
Test your DDL triggers to determine their responses to system stored procedures that are run. For example, the CREATE TYPE statement and the sp_addtype stored procedure will both fire a DDL trigger that is created on a CREATE_TYPE event. .
DDL triggers can be used for administrative tasks such as auditing and regulating database operations.
Use DDL triggers when you want to do the following:
You want to prevent certain changes to your database schema.
You want something to occur in the database in response to a change in your database schema.
You want to record changes or events in the database schema.
DDL triggers fire only after the DDL statements that trigger them are run. DDL triggers cannot be used as INSTEAD OF triggers.
The following example shows how a DDL trigger can be used to prevent any table in a database from being modified or dropped.
CREATE TRIGGER safety
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
PRINT 'You must disable Trigger "safety" to drop or alter tables!'
ROLLBACK ;
DDL triggers can fire in response to a Transact-SQL event that is processed in the current database or on the current server. The scope of the trigger depends on the event. For more information about the scope of a DDL trigger, see Designing DDL Triggers.
To obtain a DDL trigger example that is available in the AdventureWorks2008R2 sample database, in Object Explorer in the SQL Server Management Studio, open the Database Triggers folder located in the Programmability folder of the AdventureWorks2008R2 database. Right-click ddlDatabaseTriggerLog and select Script Database Trigger as. By default, the DDL trigger ddlDatabaseTriggerLog is disabled.