Ask the Expert

SQL Server 2005 DDL vs. DML triggers

What are DDL triggers? Are they different than normal triggers?

    Requires Free Membership to View

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.

This was first published in December 2005

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: