Example: Comparing Event Data to Prevent Duplicate Notifications

The following example shows how to use an event chronicle table to avoid redundant notifications based on previously received event data; only new, unique data is sent out to subscribers. In this example, a news update application notifies you about the new headlines on a particular news Web site.

Scenario

Your event-driven subscription is defined to notify you any time a new headline is posted to the news Web site. The Web site provides no way for the event provider to know when the headline changes, so the event provider collects the contents of the headline element every 15 minutes. The data for the morning is shown in the following table.

You receive a notification based on the 09:00 GMT data, containing the current headline, "Political Gridlock in Olympia." After the event batch is processed and your notification is generated, a row for the current headline is inserted in the event chronicle table by an event chronicle rule that is defined for this application.

Subscription Rule

The notification generation action defined for the subscription rule uses the event chronicle table to prevent duplicate notifications by excluding any events whose headline value already exists in the event chronicle table:

SELECT dbo.NewsNotificationNotify(S.SubscriberId,
S.SubscriberDeviceName, S.SubscriberLocale,
E.Headline, E.BrowseBackURL)
FROM dbo.NewsSubscriptions S, dbo.NewsEvents E
WHERE E.Headline 
NOT IN (SELECT Headline from dbo.NewsEventChron )
INSERT dbo.NewsEventChron(Headline)
SELECT NewsEvents.Headline
FROM dbo.NewsEvents
WHERE NewsEvents.Headline
NOT IN (SELECT Headline FROM dbo.NewsEventChron)

Results

Based on this example, the notification process proceeds as follows:

1. The first headline, "Political Gridlock in Olympia," is not in the chronicle table, and is sent to you as a notification. The chronicle table is then updated with this headline.
2. The second headline, "Political Gridlock in Olympia," is identical to an existing record (from the first headline) in the chronicle table. It is not sent to you, and the chronicle table is not updated.
3. The third headline, "Wildfire in the Cascades," is not in the chronicle table, and is sent to you as a notification. The chronicle table is then updated with this headline.

See Also

Concepts

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

Help and Information

Getting SQL Server 2005 Assistance