Defining Chronicles for a Subscription Class

The subscription chronicle tables store subscription data for use in your application. For example, you might use a subscription chronicle table to store information about the last notification that was delivered to a subscriber, and generate the next notification based only on data that has come in during the intervening time.

Common Uses for Subscription Chronicles

Subscription chronicles typically store data about previous notifications. When generating notifications, your application can use this data to determine when the subscriber last received a notification, or if the subscriber has received a similar notification.

A good example of when to use a subscription chronicle table is an application where you want to limit each subscriber to one notification per time period. You can define a subscription chronicle that contains one row per subscriber and has a time stamp indicating when the last notification was generated.

When generating notifications, you can include a condition to generate notifications only if the subscriber has not received a notification in the past 24 hours. If the subscriber has not recently received a notification, you add the notification to the notification table and update the time stamp in the subscription chronicle.

Subscription Chronicle Tables

Subscription chronicles are implemented as tables. When you define a subscription class, you can define zero, one, or multiple chronicle tables by using the Transact-SQL CREATE TABLE statement. This statement must include the table name, field names, and field data types. The statement can also include arguments for constraints and any other optional CREATE TABLE parameters. You can also include a CREATE INDEX statement to create an index on your event chronicle table. For more information, see CREATE TABLE (Transact-SQL).

Notification Services does not automatically rename subscription chronicle tables when you update the application, as it does for other subscription tables. Statements that create a chronicle table fail if a table with the same name exists. Use the INFORMATION_SCHEMA.TABLES view to verify that the table exists, and then either skip creating the table or drop and recreate the table.

The following example shows how to delete an existing table with the name dbo.SubscriberHistory, and then create a chronicle for the StockSubscriptions subscription class that has two columns (SubscriberId and NotificationTime):

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME = 'SubscriptionHistory'
        AND TABLE_SCHEMA = 'dbo')
    DROP TABLE dbo.SubscriberHistory;
CREATE TABLE dbo.SubscriberHistory
    ( 
    SubscriberId nvarchar(255), 
    LastNotified datetime 
    );

To define a subscription chronicle table

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

• Chronicle Element for SubscriptionClass/Chronicles (ADF)
• SubscriptionChronicles (NMO)

Updating Subscription Chronicles

You can define one or more queries to manipulate subscription chronicle table data. These queries insert, update, and delete data in the chronicle tables to keep the tables in an appropriate state for use by your application.

You must define subscription chronicle rules in a new event or scheduled subscription rule, or as part of a new subscription rule.

The following code shows how you might update a subscription chronicle for a stock application. The subscription chronicle tracks when a notification was last generated for each subscriber.

UPDATE dbo.SubscriberHistory
SET LastNotified = GETUTCDATE() 
FROM dbo.StockSub s 
    JOIN dbo.SubscriberHistory h
        ON s.SubscriberId = h.SubscriberId
    JOIN dbo.EventChron ec 
        ON ec.Updated > h.LastNotified;

This rule depends on using the event chronicle to generate notifications before updating the SubscriberHistory subscription chronicle. You should add this rule to the subscription rule that generates notifications, and not place it in a new subscription rule.

For more information about subscription rules, see Defining Subscription Rules.

See Also

Concepts

Defining Chronicles for an Event Class

Other Resources

Defining Subscription Classes
UPDATE (Transact-SQL)
SET (Transact-SQL)
IF...ELSE (Transact-SQL)
EXISTS (Transact-SQL)
CREATE TABLE (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance