question

Steve Jones - Editor avatar image
Steve Jones - Editor asked

Trigger differences

What is the difference between DDL and regular triggers? I keep seeing these terms in Books Online.

t-sqltrigger
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image
Matt Whitfield answered

A DDL trigger is something that works on changes to the schema of a database. They are scoped against the database. There are also server event/logon triggers, which work on extended events and are scopes against the server. A 'regular' trigger is a DML trigger, and executes either 'AFTER' or 'INSTEAD OF' an UPDATE, DELETE or INSERT statement, or any combination of the three.

A big difference is the way that the triggers deal with their scope data - DML triggers use the virtual table monikers 'inserted' and 'deleted' to reference the new and old values of data, respectively. DDL/server event/logon triggers use the EVENTDATA function to retrieve information about what happened.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jorge Segarra avatar image
Jorge Segarra answered

A nice quick article from MSDN on Understanding DDL Triggers vs DML Triggers:

http://msdn.microsoft.com/en-us/library/ms189599.aspx

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.