Event Notifications (SQL Server)

An event notification sends information about a database or server event to a service broker service. Event notifications are created only by using Transact-SQL statements. PowerDesigner models event notifications as extended objects with a stereotype of <<EventNotification>>.

Creating an Event Notification

You can create an event notification in any of the following ways:

  • Select Model > Event Notifications to access the List of Event Notifications, and click the Add a Row tool.

  • Right-click the model (or a package) in the Browser, and select New > Event Notification.

Event Notification Properties

You can modify an object's properties from its property sheet. To open an event notification property sheet, double-click its Browser entry.

The following extended attributes are available on the Microsoft tab:

Name

Description

Applies on

Specifies the scope of the event notification. You can choose between:

  • database – the notification fires whenever the specified event in the FOR clause occurs anywhere in the instance of SQL Server.

  • server - the notification fires whenever the specified event in the FOR clause occurs in the current database.

  • queue - the notification fires whenever the specified event in the FOR clause occurs in the current queue. Can be specified only if FOR QUEUE_ACTIVATION or FOR BROKER_QUEUE_DISABLED is also specified.

Scripting name: AppliesOn

Queue

Specifies the queue to which the event notification applies. Available only if Applies on is set to "queue".

Scripting name: Queue

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 SID).

  • specify the same service and broker_instance_specifier.

  • specify WITH FAN_IN.

Scripting name: WithFanIn

Events

Specifies the name of the event type that causes the event notification to execute. Can be a Transact-SQL DDL, SQL Trace, or Service Broker event type.

Scripting name: Events

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. See Services (SQL Server).

Scripting name: Service

Instance

Specifies a service broker instance against which broker_service is resolved. Use 'current database' to specify the service broker instance in the current database.

Scripting name: Instance