Understanding DDL triggers in SQL Server 2005
Understanding DDL Triggers in SQL Server 2005Much like regular DML triggers, DDL triggers fire in response to an event happening on the server. However, DDL triggers do not fire in response to UPDATE, INSERT, or DELETE statements on a table or view. Instead, they fire in response to Data Definition Language (DDL) statements that start with the keywords CREATE, ALTER, and DROP.
You can use DDL triggers to audit and regulate database operations. For instance, you can write a DDL trigger to prevent certain changes to your database schema, or maybe allow the change but record it in a log table for auditing purposes.
Certain system stored procedures that perform DDL-like operations can also fire DDL triggers. The CREATE TYPE statement and the sp_addtype stored procedure can both fire a DDL trigger. However, not all system stored procedures fire DDL triggers. The sp_rename stored procedure for instance does not fire any DDL triggers. You need to test your DDL triggers to determine their responses to system stored procedures.
DDL triggers can be server-scoped or database-scoped. A database-scoped DDL trigger is simply called a database trigger. The following example shows how a database trigger can be used to prevent any table in the current database from being modified or dropped. Keep in mind that DDL triggers fire only after the DDL statements that trigger them are run:
CREATE TRIGGER trigger1
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE AS
PRINT ‘You cannot drop or alter this table.’
ROLLBACK
You can temporarily disable a DDL trigger. Disabling a DDL trigger does not drop it. The trigger will still exists as an object; however, it will not be triggered by any of statements on which it was programmed. DDL triggers that are disabled can be re-enabled. Enabling a DDL trigger causes it to fire in the same way the trigger did when it was originally created.
Triggers can be nested up to 32 levels maximum. If two or more triggers are created for a table, the firing of one trigger will lead to the cascading firing of other triggers. If any one trigger in the chain goes into a loop the trigger is cancelled. Nested triggers can be disabled using the sp_configure to 0. When nested triggers are set to 0, recursive triggers are also disabled.
DDL triggers can be used to perform the following operations
1. Prevent changes to Database schema
2. Fire events in response to changes in the database schema
3. Record changes or events in the database schema
DDL Triggers vs DML Triggers
While DML triggers operate on INSERT, UPDATE and DELETE statements to enforce business rules and data integrity checks, DDL Triggers operate on CREATE, ALTER, DROP and other DDL statements and enforce business rules that affect databases across servers. Unlike DML triggers, DDL triggers are not scoped to schemas. However, both the types of triggers are created, modified and dropped using similar T_SQL syntax. They run managed code packaged in an assembly created in the Microsoft .NET framework and uploaded in the SQL server. Moreover, like DML triggers, DDL triggers can create and use more than one trigger on the same T_SQL Statement. The DDL trigger and the statement that fires the trigger are run within the same transaction. This transaction can be rolled back from within the trigger when there is an error. If a DDL trigger is run from a batch and explicitly includes the ROLLBACK TRANSACTION statement it will cancel the whole batch. Finally both DDL and DML triggers can be nested
