Designing Event Notifications
To design an event notification, you must determine the following:
- The scope of the notification.
- The Transact-SQL statement, or group of statements, that raises the event notification.
Defining Notification Scope
You can specify an event notification to occur in response to a statement made on all objects in the current database or all objects on an instance of SQL Server. Event notifications specified on the QUEUE_ACTIVATION and BROKER_QUEUE_DISABLED events are scoped to individual queues. Not all events can occur at any scope. CREATE_DATABASE events, for example, can occur only at the server instance level. Conversely, an event notification created on an ALTER_TABLE event can be programmed to occur on all tables in the database or on all tables on the server instance.
The following example sends a notification of any
ALTER TABLE statement run on the server instance to the Service Broker instance in the current database.
CREATE EVENT NOTIFICATION log_ddl1 ON SERVER FOR ALTER_TABLE TO SERVICE '//Adventure-Works.com/ArchiveService' , 'current database';
Links to the Transact-SQL statements and the scopes that can be specified for them are provided in the section "Selecting a Particular DDL Statement to Raise an Event Notification" below.
Event notifications do not occur in response to events that affect local or global temporary tables and stored procedures.
Specifying a Transact-SQL Statement or Group of Statements
Event notifications can be created to occur in response to the following:
- A particular DDL statement, SQL Trace event, or Service Broker event
- A predefined group of DDL statements or SQL Trace events
Selecting a Particular DDL Statement to Raise an Event Notification
Event notifications can be designed to occur after a particular Transact-SQL statement or stored procedure is run. As shown in the previous example, that event notification occurs after an
A list of the individual Transact-SQL statements that can be specified to raise an event notification and the scope at which the notifications can execute is provided in DDL Statements for Use with Event Notifications. These events can also be obtained by querying the sys.event_notification_event_types catalog view.
Certain system stored procedures that perform DDL-like operations can also fire event notifications. Test your event notifications to determine their responses to system stored procedures that are run. For example, the CREATE TYPE statement and the sp_addtype stored procedure will both fire an event notification that is created on a CREATE_TYPE event. However, the sp_rename stored procedure does not fire any event notifications.
Selecting a Particular SQL Trace Event to Raise an Event Notification
Event notifications can be designed to fire after a SQL Trace event occurs. For example, the following event notification fires after an
Object_Created event on the server.
CREATE EVENT NOTIFICATION log_ddl1 ON SERVER FOR Object_Created TO SERVICE '//Adventure-Works.com/ArchiveService', 'current database' ;
For a list of the SQL Trace events that can raise an event notification, see Trace Events for Use with Event Notifications. You can also obtain a list of these events by querying the sys.event_notification_event_types catalog view. SQL Trace events can be executed only at the server instance scope. For more information about SQL Trace event classes, see SQL Server Profiler Reference.
Selecting a Service Broker Event to Raise an Event Notification
Event notifications can be designed to fire after a QUEUE_ACTIVATION or BROKER_QUEUE_DISABLED Service Broker event. The QUEUE_ACTIVATION event occurs when a queue has messages to process. For more information, see Event-Based Activation. The BROKER_QUEUE_DISABLED event occurs when the status of a queue is set to OFF. For more information, see Handling Poison Messages.
Selecting a Predefined Group of DDL Statements to Raise an Event Notification
An event notification can occur after any Transact-SQL event that belongs to a predefined grouping of similar events is run. For example, if you want an event notification to occur after any CREATE TABLE, ALTER TABLE, or DROP TABLE statement is executed, you can specify FOR DDL_TABLE_EVENTS in the CREATE EVENT NOTIFICATION statement. After CREATE EVENT NOTIFICATION executes, the events that are covered by an event group are added to the sys.events catalog view.
For a list of the predefined groups of DDL and DML statements that are available for event notifications, the particular statements they cover, and the scope at which these event groups can execute, see DDL Event Groups for Use with Event Notifications.
Selecting a Predefined Group of SQL Trace Events to Raise an Event Notification
An event notification can occur after any SQL Trace event that belongs to a predefined grouping of similar trace events is run. For example, if you want an event notification to occur after any locking-related trace event, which includes the LOCK_DEADLOCK, LOCK_DEADLOCK_CHAIN, LOCK_ESCALATION, and DEADLOCK_GRAPH events, you can specify FOR TRC_LOCKS in the CREATE EVENT NOTIFICATION statement.
For a list of the predefined groups of SQL Trace events that are available for event notifications, see Trace Event Groups for Use with Event Notifications. These groups can execute only at the server instance level.
Help and Information
14 April 2006
5 December 2005