EXPERT RESPONSE
SQL Server has supported triggers for several versions. These "normal" triggers are known as DML (Data Manipulation Language) triggers, and fire when data modifications occur as the result of INSERT, UPDATE, or DELETE statements.
SQL Server 2005 adds a new type of trigger, DDL (Data Definition Language) triggers. These triggers fire when database or server metadata modifications occur as the result of CREATE, ALTER, or DROP statements. For instance, a DDL trigger can be created that will fire every time a user is added to a database, or every time a login is added to the server.
There are a few important distinctions that must be drawn between DDL triggers and DML triggers. First of all, DML triggers are created on tables, at the database level. DDL triggers, on the other hand, can be created at either the database or the server level. This allows DDL triggers to be created to capture server events such as ALTER LOGIN or ALTER DATABASE, in addition to database-level events such as ALTER TABLE or ALTER PROCEDURE.
The second major distinction is that only AFTER DDL triggers are supported. SQL Server 2000 supports INSTEAD OF DML triggers that fire instead of the data modification instead of afterwards. This is especially important because one of the major use cases for DDL triggers is restricting certain operations; for instance, you might have a DDL trigger that rolls back any ALTER TABLE operations for certain tables. But because the trigger fires after the operation completes, the entire operation will have to be rolled back—a potentially expensive process. This is something to watch out for, but in the end it's a small price for the functionality afforded by DDL triggers.
The final difference is that DDL triggers, unlike DML triggers, do not make use of the inserted and deleted virtual tables. Instead, you'll use the EVENTDATA function to get information about what event caused the trigger to fire. For more information about EVENTDATA, consult SQL Server 2005 Books Online.
Do you have comments on this Ask the Expert Q&A? Let us know.
|
