Defining Actions

An action is a group of Transact-SQL statements that Notification Services runs each time it fires a subscription rule. Each subscription rule can contain one action, either a basic action, which is a predefined query, or a condition action, which allows subscribers to define the equivalent of a WHERE clause for the notification generation query. This topic describes basic actions and how to write them.

Actions

The Transact-SQL statements in an action perform some work for the rule. This work typically is to generate notifications based on a join between subscription fields and event fields. The following example shows an action that generates notifications for a weather application:

INSERT INTO WeatherNotifications (SubscriberId, DeviceName, 
    SubscriberLocale, City, Forecast)
SELECT s.SubscriberId, s.DeviceName, 
   s.SubscriberLocale, e.City, e.Forecast
FROM WeatherEvents e 
JOIN WeatherSubscriptions s
ON s.City = e.City;

This example selects the subscriber, device, locale, city, and forecast text from a join between the WeatherEvents event view and the WeatherSubscriptions subscription table, and then adds the results to the WeatherNotifications notification table. Notification Services automatically creates the WeatherEvents view for the WeatherEvents event class; this view contains only the current set of events to be processed by the generator.

Actions do not need to use the events view and the subscriptions table. For example, you can query tables in other databases.

Also, actions are not required to generate notifications. However, at least one subscription rule should have an action that generates notifications. Otherwise, your application will not generate and send notifications to subscribers.

For more information about writing Transact-SQL queries, see Query Fundamentals.

The INSERT Statement

As illustrated in the code example, you must specify the following fields, in the following order, in the INSERT statement:

• SubscriberId
• DeviceName
• SubscriberLocale
• All non-computed fields defined in the notification schema. If you use computed fields, do not insert values into these fields; the values for those fields are computed when you insert the notification data.

Add to the notification table only within a subscription rule. When processing subscription rules, Notification Services prepares each rule firing, fires the rules, and then cleans up after the rule firing. If you try to insert notifications outside of a subscription rule firing, the preparation and cleanup does not happen, which causes failures.

Using a WHERE Clause for Additional Conditions

If your subscription schema has more than one custom field, you can add a WHERE clause to provide additional conditions. For example, if the weather application allows subscribers to enter a temperature range, you could add the following clause to the previous code example:

WHERE  e.HighTemp > s.High 
    OR e.LowTemp < s.Low

Because of this WHERE clause, the action will only generate notifications if the HighTemp value in the event is above the range entered by the subscriber or the LowTemp value in the event is below the range entered by the subscriber.

Other Clauses

Transact-SQL supports many other clauses for the SELECT statement. However, most clauses are not relevant to Notification Services. For example, the ORDER BY clause orders the results of the SELECT query, but this order does not affect how or when Notification Services formats and delivers notifications. The UNION clause can be used to combine the results of two queries, but this is rarely used for subscription rules.

Using Stored Procedures

Instead of embedding the Transact-SQL statements in the action, the action can call a stored procedure. You must create the stored procedure in the application database. You can define the stored procedure in a deployment script. You should create the stored procedure after Notification Services creates the instance or adds the application, but before you enable the instance or application.

To use a stored procedure, execute the stored procedure from the action. The following example shows an action that executes a stored procedure:

EXECUTE dbo.WeatherActionSP;

Transactions

All statements in an action are part of the same transaction, so either they will all complete successfully or they all will be rolled back. If the transaction fails, Notification Services will write an error to the Windows application log.

Actions for Event Rules

Event rule actions typically select event data from a view named after the event class. This event view provides only the current set of events, which ensures that the action does not use old events from the event table.

Template

The following template shows the basic structure of an action for an event rule. This action template shows how to join events from the event view and subscriptions from the subscriptions view and insert the results into the notifications view.

INSERT INTO schema.notificationClassName (SubscriberId, DeviceName, 
    SubscriberLocale, NotificationFields)
SELECT s.SubscriberId, DeviceName, SubscriberLocale, Fields
FROM schema.subscriptionClassName s 
JOIN schema.eventClassName e
ON s.columnName = e.columnName
[WHERE...][;]

You can either select the DeviceName and SubscriberLocale values from a data source, such as the subscription view, or provide literal values, such as 'File' and 'en-US'.

Note that the SubscriberId and DeviceName values must match a record in the SubscriberDevices table.

Event Chronicle Maintenance

You can also use event rules to maintain event chronicles. This allows the event rule to use the old event chronicle to check previous event values before generating notifications. You can either create a new event rule for chronicle maintenance or add the chronicle-maintenance code to the statements for an existing event rule.

The following rule first deletes all data from the event chronicle. The rule then selects the current batch of events from the WeatherEvents view and adds the events to the event chronicle.

DELETE FROM dbo.WeatherEventsChron;
INSERT INTO dbo.WeatherEventsChron(City, Date, Low, High, Forecast)
SELECT e.City, e.Date, e.Low, e.High, e.Forecast
FROM dbo.WeatherEvents e;

If you want to update the event chronicle before generating notifications, define the event chronicle rule in the event class. For more information, see Defining Event Chronicle Rules.

To define an action for an event rule

If you are defining an application through XML, define actions in the application definition file (ADF). If you are defining an application programmatically, use Notification Services Management Objects (NMO) to define actions.

• Action Element for EventRule (ADF)
• Action property (NMO)

Actions for Schedule Rules

Scheduled rule actions typically select event data from an event chronicle, not the event view. The event view provides only the current event batch and might be empty when the scheduled rule runs.

Template

The following template shows the basic structure of an action for a scheduled rule. This action template shows how to join events from an event chronicle and subscriptions from the subscriptions view, and then add the results to the notifications view.

INSERT INTO schema.notificationClassName (SubscriberId, DeviceName, 
    SubscriberLocale, NotificationFields)
SELECT s.SubscriberId, DeviceName, SubscriberLocale, Fields
FROM schema.subscriptionClassName s 
JOIN schema.eventChronicleName ec
ON s.columnName = ec.columnName
[WHERE...][;]

In the SELECT statement, you can either select the DeviceName and SubscriberLocale values from a data source, such as the subscription view, or provide literal values, such as 'File' and 'en-US'.

Subscription Chronicle Maintenance

You can also use scheduled rules to maintain subscription chronicles. For more information about subscription chronicles, see Defining Chronicles for a Subscription Class. You can either create a new scheduled rule for chronicle maintenance or add the chronicle-maintenance code to the statements for an existing scheduled rule.

To define an action for a scheduled rule

If you are defining an application through XML, define actions in the application definition file (ADF). If you are defining an application programmatically, use NMO for to define actions.

• Action Element for ScheduledRule (ADF)
• Action property (NMO)

See Also

Concepts

Defining Condition Actions
Defining Event Rules
Defining Scheduled Rules

Other Resources

WHERE (Transact-SQL)
INSERT (Transact-SQL)
SELECT (Transact-SQL)
Defining Subscription Classes

Help and Information

Getting SQL Server 2005 Assistance