Home > SQLServer, Working With Data > SQL Server – DDL vs. DML Triggers

SQL Server – DDL vs. DML Triggers

Although, DML and DDL triggers are used for different purposes, there are some similarities and differences between these.

 

Similarities between DDL and DML triggers:

1. Both are created using similar T-SQL syntax

2. Both can run .NET code

3. You can create multiple DDL and/or DML triggers on an object

4. The trigger and the triggering statement run as part of the same transaction

5. Both type of triggers can be nested

 

Differences between DDL and DML triggers:

1. There are no INSTEAD OF DDL triggers, they are only executed after the triggering statement is completed.

2. DML triggers use virtual tables INSERTED and DELETED which captures data modification, DDL triggers do not have this

3. DDL triggers captures information about the event that fired them using EVENTDATA() function.

 

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

About these ads
  1. Rahul Jagtap
    December 8, 2012 at 1:05 am

    Hi Vishal,

    Your post of below line on “http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/7cfc7777-be11-47ac-a356-0048ffbaac41″ made a big difference , I was struggling a lot to do the thing on batch file and you below line made a big difference to my efforts. Thanks a lot !

    —> Please note that error can be captured only if severity level is greater than 10.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 263 other followers

%d bloggers like this: