Q

SQL Server 2005 DDL vs. DML triggers

SQL Server 2005 adds a new type of trigger -- DDL (Data Definition Language) triggers. Find out how they compare to "normal" DML triggers in this expert response.

What are DDL triggers? Are they different than normal triggers?
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

Dig deeper on Microsoft SQL Server 2005

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close