Tuesday 18 March 2014

What are differences between DDL Triggers and DML Triggers?

In some ways, DDL triggers are similar to DML triggers, but there are some significant differences. Both types of triggers are executed in response to events, but the types of events are different. DML triggers execute in response to INSERT, UPDATE, and DELETE statements, where DDL triggers execute in response to DDL operations. DML triggers are scoped at the table level, where DDL triggers can be scoped at the database or server-instance level.

DML Triggers
DDL Triggers
Respond to INSERT, UPDATE, and DELETE statements
Respond to CREATE, ALTER, and DROP statements
Scoped per table
Scoped at database or server instance
Create inserted and deleted tables to give information about the event that occurred
Don't create any tables. Information about the DDL operation is retrieved using the EVENTDATA() function
Can create INSTEAD OF and AFTER triggers
DDL triggers always execute after a DDL operation has occurred. You cannot create INSTEAD OF DDL triggers.

No comments:

Post a Comment