Page 110 - DCAP508_DATABASE_ADMINISTRATION

Database Administration

Notes 7.2.3 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
ALTER_TABLE event.

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.

Notes 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

104 LOVELY PROFESSIONAL UNIVERSITY