Printed 2017/07/20 06:52PM

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!


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

Filed under: SQLServer, Working With Data
Copyright © 2002-2017 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.