CREATE EVENT NOTIFICATION (Transact-SQL)
Creates an object that sends information about a database or server event to a service broker service. Event notifications are created only by using Transact-SQL statements.
Service Broker includes a message type and contract specifically for event notifications. Therefore, a Service Broker initiating service does not have to be created because one already exists that specifies the following contract name: http://schemas.microsoft.com/SQL/Notifications/PostEventNotification
The target service that receives event notifications must honor this preexisting contract. For more information about creating Service Broker target services, see Benefits of Programming with Service Broker.
 Important |
|---|
Service Broker dialog security should be configured for event notifications that send messages to a service broker on a remote server. Dialog security must be configured manually according to the full security model. For more information, see Dialog Security for Event Notifications. |
If an event transaction that activates a notification is rolled back, the sending of the event notification is also rolled back. Event notifications do not fire by an action defined in a trigger when the transaction is committed or rolled back inside the trigger. Because trace events are not bound by transactions, event notifications based on trace events are sent regardless of whether the transaction that activates them is rolled back.
If the conversation between the server and the target service is broken after an event notification fires, an error is reported and the event notification is dropped.
The event transaction that originally started the notification is not affected by the success or failure of the sending of the event notification.
Any failure to send an event notification is logged.
To create an event notification that is scoped to the database (ON DATABASE), requires CREATE DATABASE DDL EVENT NOTIFICATION permission in the current database.
To create an event notification on a DDL statement that is scoped to the server (ON SERVER), requires CREATE DDL EVENT NOTIFICATION permission in the server.
To create an event notification on a trace event, requires CREATE TRACE EVENT NOTIFICATION permission in the server.
To create an event notification that is scoped to a queue, requires ALTER permission on the queue.
| Note |
|---|
In Examples A and B below, the GUID in the TO SERVICE 'NotifyService' clause ('8140a771-3c4b-4479-8ac0-81008ab17984') is specific to the computer on which the example was set up. For that instance, that was the GUID for the AdventureWorks database. To copy and run these examples, you need to replace this GUID with one from your computer and SQL Server instance. As explained in the Arguments section above, you can acquire the 'broker_instance_specifier' by querying the service_broker_guid column of the sys.databases catalog view. |
A. Creating an event notification that is server scoped
The following example creates the required objects to set up a target service using Service Broker. The target service references the message type and contract of the initiating service specifically for event notifications. Then an event notification is created on that target service that sends a notification whenever an Object_Created trace event happens on the instance of SQL Server.
--Create a queue to receive messages.
CREATE QUEUE NotifyQueue ;
GO
--Create a service on the queue that references
--the event notifications contract.
CREATE SERVICE NotifyService
ON QUEUE NotifyQueue
([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
GO
--Create a route on the service to define the address
--to which Service Broker sends messages for the service.
CREATE ROUTE NotifyRoute
WITH SERVICE_NAME = 'NotifyService',
ADDRESS = 'LOCAL';
GO
--Create the event notification.
CREATE EVENT NOTIFICATION log_ddl1
ON SERVER
FOR Object_Created
TO SERVICE 'NotifyService',
'8140a771-3c4b-4479-8ac0-81008ab17984' ;
B. Creating an event notification that is database scoped
The following example creates an event notification on the same target service as the previous example. The event notification fires after an ALTER_TABLE event occurs on the AdventureWorks sample database.
CREATE EVENT NOTIFICATION Notify_ALTER_T1
ON DATABASE
FOR ALTER_TABLE
TO SERVICE 'NotifyService',
'8140a771-3c4b-4479-8ac0-81008ab17984';
C. Getting information about an event notification that is server scoped
The following example queries the sys.server_event_notifications catalog view for metadata about event notification log_ddl1 that was created with server scope.
SELECT * FROM sys.server_event_notifications WHERE name = 'log_ddl1'
D. Getting information about an event notification that is database scoped
The following example queries the sys.event_notifications catalog view for metadata about event notification Notify_ALTER_T1 that was created with database scope.
SELECT * FROM sys.event_notifications WHERE name = 'Notify_ALTER_T1'
