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.
Related Q&A from Adam Machanic
Migrating to SQL Server 2005 from SQL Server 2000 is a hefty feat when compared to upgrading from 7.0 to SQL Server 2000. Site expert Adam Machanic ...continue reading
Multiple readers can sometimes read the same row simultaneously causing a false result. SQL Server 2005 expert Adam Machanic suggests modifying the ...continue reading
Database servers needed for a site with thousands of users can vary depending on a number of factors, including your necessary level of load ...continue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.