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.
Note
Event notifications do not occur in response to events that affect local or global temporary tables and stored procedures.
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" that follows.
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 ALTER_TABLE event.
For 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, see DDL Events. These events can also be obtained by querying the sys.event_notification_event_types catalog view.
Note
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.
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 event group is added to the sys.events catalog view.
Note
In SQL Server 2005, sys.events expands event groups by listing only the individual events that are covered by a group. Therefore, changes to the events covered by event groups in SQL Server 2008 do not apply to event notifications that are created on those event groups in SQL Server 2005.
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.
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.
See Also