Create event notifications for Azure resources
In this we will learn to create an object that sends information about a database or server event to a service broker service. However, event notifications are created only by using Transact-SQL statements.
Syntax
syntaxsql
CREATE EVENT NOTIFICATION event_notification_name
ON { SERVER | DATABASE | QUEUE queue_name }
[ WITH FAN_IN ]FOR { event_type | event_group } [ ,…n ]TO SERVICE ‘broker_service’ , { ‘broker_instance_specifier’ | ‘current database’ }
[ ; ]
Arguments
event_notification_name
Is the name of the event notification. However, an event notification name must comply with the rules for identifiers and must be unique within the scope in which they are created: SERVER, DATABASE, or object_name.
SERVER
Applies the scope of the event notification to the current instance of SQL Server. However, if specified, the notification fires whenever the specified event in the FOR clause occurs anywhere in the instance of SQL Server.
DATABASE
Applies the scope of the event notification to the current database. However, 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. However, 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.
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
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.
‘ 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. However, this service must honor the same SQL Server Events message type and contract that is used to send the message.
Remarks
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: https://schemas.microsoft.com/SQL/Notifications/PostEventNotification
However, 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.
Permissions
- Firstly, to create an event notification that is scoped to the database (ON DATABASE), requires CREATE DATABASE DDL EVENT NOTIFICATION permission in the current database.
- Secondly, 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.
- Next, to create an event notification on a trace event, requires CREATE TRACE EVENT NOTIFICATION permission in the server.
- Lastly, to create an event notification that is scoped to a queue, requires ALTER permission on the queue.
Examples
Creating an event notification that is server scoped
The following example creates the required objects to set up a target service using Service Broker.
Creating an event notification that is database scoped
The following example creates an event notification on the same target service as the previous example.
SQL
CREATE EVENT NOTIFICATION Notify_ALTER_T1
ON DATABASE
FOR ALTER_TABLE
TO SERVICE ‘NotifyService’,
‘8140a771-3c4b-4479-8ac0-81008ab17984’;
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.
SQL
SELECT * FROM sys.server_event_notifications
WHERE name = ‘log_ddl1’;
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.
SQL
SELECT * FROM sys.event_notifications
WHERE name = ‘Notify_ALTER_T1’;
Reference: Microsoft Documentation