Understanding Event Notifications vs. Triggers
The following table compares and contrasts triggers and event notifications.
TRIGGERS |
EVENT NOTIFICATIONS |
|---|---|
DML triggers respond to data manipulation language (DML) events. DDL triggers respond to data definition language (DDL) events. |
Event notifications respond to DDL events and a subset of SQL trace events. |
Triggers can run Transact-SQL or common language runtime (CLR) managed code. |
Event notifications do not run code. Instead, they send xml messages to a Service Broker service. |
Triggers are processed synchronously, within the scope of the transactions that cause them to fire. |
Event notifications may be processed asynchronously and do not run in the scope of the transactions that cause them to fire. |
The consumer of a trigger is tightly coupled with the event that causes it to fire. |
The consumer of an event notification is decoupled from the event that causes it to fire. |
Triggers must be processed on the local server. |
Event notifications can be processed on a remote server. |
Triggers can be rolled back. |
Event notifications cannot be rolled back. |
DML trigger names are schema-scoped. DDL trigger names are database-scoped or server-scoped. |
Event notification names are scoped by the server or database. Event notifications on a QUEUE_ACTIVATION event are scoped to a specific queue. |
DML triggers are owned by the same owner as the tables on which they are applied. |
The owner of an event notification on a queue may have a different owner than the object on which it is applied. |
Triggers support the EXECUTE AS clause. |
Event notifications do not support the EXECUTE AS clause. |
DDL trigger event information can be captured using the EVENTDATA function, which returns an xml data type. |
Event notifications send xml event information to a Service Broker service. The information is formatted to the same schema as that of the EVENTDATA function. |
Metadata about triggers is found in the sys.triggers and sys.server_triggers catalog views. |
Metadata about event notifications is found in the sys.event_notifications and sys.server_event_notifications catalog views. |