Understanding Event Notifications vs. Triggers

The following table compares and contrasts triggers and 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.

Community Additions