SQL - Event Notifications vs. Triggers

Event Notifications

This is also a very beautiful feature of SQL Server which was introduced with the release of SQL Server 2005. Event Notifications mechanism is used for tracking all events or changes happening at the database or instance level. Event Notifications convey the very same data as DDL triggers and occur on the very same events, but they are asynchronous and loosely coupled as SQL Trace.

Triggers

Triggers are extremely powerful and useful feature of SQL because they are completely Global and Stealthy They are good and necessary for data integrity as well as complete the DBMS-specific activity under the control of the database where it belongs. They are basically used for audit logging.

“Triggers are stored programs, which are automatically executed or fired when some events occur on the table like insertion, deletion or updation of data. It is a database object which could be defined on the table, view, schema, or database with which the event is associated”.

Event Notifications vs. Triggers :

T-SQL vs PL/SQL is going to vary in importance depending on whether you write database software (scripts, stored procedures, functions, etc.), or if you are more purely administrative. 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 T-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.