Understanding Event Notifications
Event notifications execute in response to a variety of Transact-SQL data definition language (DDL) statements and SQL Trace events by sending information about these events to a Service Broker service.
Event notifications can be used to do the following:
Log and review changes or activity occurring on the database.
Perform an action in response to an event in an asynchronous instead of synchronous manner.
Event notifications can offer a programming alternative to DDL triggers and SQL Trace.
Event notifications run asynchronously, outside the scope of a transaction. Therefore, unlike DDL triggers, event notifications can be used inside a database application to respond to events without using any resources defined by the immediate transaction. For more information, see Understanding Event Notifications vs. Triggers.
Unlike SQL Trace, event notifications can be used to perform an action inside an instance of SQL Server in response to a SQL Trace event. For more information, see Understanding Event Notifications vs. SQL Trace.
When an event notification is created, one or more Service Broker conversations between an instance of SQL Server and the target service you specify are opened. The conversations typically remain open as long as the event notification exists as an object on the server instance. In some error cases the conversations can close before the event notification is dropped. These conversations are never shared between event notifications. Every event notification has its own exclusive conversations. Ending a conversation explicitly prevents the target service from receiving more messages, and the conversation will not reopen the next time the event notification fires.
Event information is delivered to the Service Broker as a variable of type xml that provides information about when an event occurs, about the database object affected, the Transact-SQL batch statement involved, and other information. For more information about the XML schema produced by event notifications, see EVENTDATA (Transact-SQL).
Event data can be used by applications that are running together with SQL Server to track progress and make decisions. For example, the following event notification sends a notice to a certain service every time an ALTER TABLE statement is issued in the AdventureWorks2008R2 sample database.
USE AdventureWorks2008R2;
GO
CREATE EVENT NOTIFICATION NotifyALTER_T1
ON DATABASE
FOR ALTER_TABLE
TO SERVICE '//Adventure-Works.com/ArchiveService' ,
'8140a771-3c4b-4479-8ac0-81008ab17984';
An event notifications sample script that can be run against the AdventureWorks2008R2 sample database, EventNotificationSample.sql, is provided in SQL Server Database Engine Samples.