Share via


Example: Using an Event Chronicle for Scheduled Subscriptions

The following example shows how to use an event chronicle with a Notification Services event class to provide event data for scheduled subscriptions. In this example, a weather update application notifies users about the forecast for their area each day.

Scenario

A Notification Services application collects weather forecast data and sends weather notifications to subscribers. Each subscription specifies a city for which to collect weather information and a schedule for receiving notifications. When a subscription's scheduled time occurs, the application sends the city's most recent weather forecast to the subscriber.

The application has an event chronicle that stores the most recent weather forecast for all major cities. In this application, each event batch contains forecasts for all cities that are supported by the application. When a batch of weather forecasts arrive, the event chronicle rule deletes old forecasts and adds new forecasts to the event chronicle.

Event Chronicle Table

In the event class, you must define the event chronicle table. The chronicle uses the same columns as the event class (City, Date, Low, High, and Forecast).

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME = 'WeatherEventsChron'
        AND TABLE_SCHEMA = 'dbo')
    DROP TABLE dbo.WeatherEventsChron;
CREATE TABLE dbo.WeatherEventsChron
    (
    [City]nvarchar(35),
    [Date]datetime,
    [Low]float,
    [High]float,
    [Forecast]nvarchar(3500)
    PRIMARY KEY (City)
    );

When you update the application, if any changes are made to the event chronicle, Notification Services drops the current table and creates a new table. For more information about this syntax, see Defining Event Chronicle Tables.

Event Chronicle Rule

Also in the event class, you define an event chronicle rule that runs each time a batch of events arrives. The following rule first deletes all data from the event chronicle. The rule then selects the current batch of events from the WeatherEvents view, which contains the current events for the event class, 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;

For more information, see Defining Event Chronicle Rules.

Scheduled Rule Used to Generate Notifications

The scheduled rule used to generate notifications uses the event chronicle as the event source. This ensures that scheduled subscriptions have event data.

INSERT INTO dbo.WeatherNotifications(SubscriberId,
    DeviceName, SubscriberLocale,
    City, Date, Low, High, Forecast)
SELECT s.SubscriberId,
    s.DeviceName, s.SubscriberLocale,
    c.City, c.Date, c.Low, c.High, c.Forecast
FROM dbo.WeatherSubscriptions s JOIN dbo.WeatherEventsChron c
    ON s.City = c.City;

Results

At 8:00 A.M., a new batch of events arrives with the latest weather information. The event chronicle rule runs and replaces the old chronicle data with the new event data.

At 8:15 A.M., several scheduled subscriptions are due to be evaluated. The generator runs the scheduled rule and generates notifications using the event data in the event chronicle.

See Also

Concepts

Defining Chronicles for an Event Class
Defining Event Chronicle Tables
Defining Event Chronicle Rules
Example: Comparing Event Data to Prevent Duplicate Notifications
Example: Using Event Data High Values to Prevent Duplicate Notifications

Help and Information

Getting SQL Server 2005 Assistance