Create an event notification trigger, that may in turn send event information to a service broker.
Syntax
CREATE EVENT NOTIFICATION event_notification
ON { SERVER | DATABASE | QUEUE queue }
[WITH FAN_IN ]
FOR {event_type | event_group } [ ,...n ]
TO SERVICE 'broker_service' ,
{'broker_instance_specifier' | 'current database' } [; ]
Key
notification_name Name of the event notification to remove.
SERVER Apply to the current server, this option is not available in a contained database.
DATABASE Applies the scope of the event notification to the current database.
If specified, the notification fires whenever the specified event in the FOR
clause occurs in the current database.
QUEUE Applies the scope of the notification to a specific queue in the current database.
QUEUE can be specified only if FOR QUEUE_ACTIVATION or FOR BROKER_QUEUE_DISABLED is
also specified.
queue_name Is the name of the queue to which the event notification applies.
queue_name can be specified only if QUEUE is specified.
WITH FAN_IN Instructs SQL Server to send only one message per event to any specified service
for all event notifications that:
- Are created on the same event.
- Are created by the same principal (as identified by the same SID).
- Specify the same service and broker_instance_specifier.
- Specify WITH FAN_IN.
For example, three event notifications are created. All event notifications specify FOR
ALTER_TABLE, WITH FAN_IN, the same TO SERVICE clause, and are created by the same SID.
When an ALTER TABLE statement is run, the messages that are created by these three
event notifications are merged into one.
Therefore, the target service receives only one message of the event.
event_type Is the name of an event type that causes the event notification to execute.
event_type can be a Transact-SQL DDL event type, a SQL Trace event type, or a Service
Broker event type. For a list of qualifying Transact-SQL DDL event types, see DDL Events.
Service Broker event types are QUEUE_ACTIVATION and BROKER_QUEUE_DISABLED.
For more information, see Event Notifications.
event_group Is the name of a predefined group of Transact-SQL or SQL Trace event types.
An event notification can fire after execution of any event that belongs to an event
group. For a list of DDL event groups, the Transact-SQL events they cover, and the
scope at which they can be defined, see DDL Event Groups. event_group also acts as a
macro, when the CREATE EVENT NOTIFICATION statement finishes, by adding the event types
it covers to the sys.events catalog view.
'broker_service' Specifies the target service that receives the event instance data.
SQL Server opens one or more conversations to the target service for the event
notification. This service must honor the same SQL Server Events message type and
contract that is used to send the message.
The conversations remain open until the event notification is dropped.
Certain errors could cause the conversations to close earlier.
Ending some or all conversations explicitly might prevent the target service from
receiving more messages.
{ 'broker_instance_specifier' | 'current database' }
Specifies a service broker instance against which broker_service is resolved.
The value for a specific service broker can be acquired by querying the
service_broker_guid column of the sys.databases catalog view.
Use 'current database' to specify the service broker instance in the current database.
'current database' is a case-insensitive string literal.
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.
Options must must be specified as they originally were when the event notification was created.
Create an event notification that is server scoped:
--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
([https://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' ;
Create an event notification that is database scoped:
CREATE EVENT NOTIFICATION Notify_ALTER_T1
ON DATABASE
FOR ALTER_TABLE
TO SERVICE 'NotifyService',
'8140a771-3c4b-4479-8ac0-81008ab17984';
Get information about an event notification that is server scoped:
SELECT * FROM sys.server_event_notifications WHERE name = 'log_ddl1';
Get information about an event notification that is database scoped:
SELECT * FROM sys.event_notifications WHERE name = 'Notify_ALTER_T1';
“Even a minor event in the life of a child is an event of that child's world and thus a world event” ~ Gaston Bachelard
DROP EVENT NOTIFICATION
ALTER ANY DATABASE EVENT NOTIFICATION (Database)
ALTER ANY EVENT NOTIFICATION (Server)
