Defining Condition Actions

  • Article

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 condition actions and how to write them.

Important

Use condition actions only if you need to allow subscribers to create their own complex conditions for generating notifications. Otherwise, use predefined actions that allow users to enter values for a parameterized query, which is more efficient. For more information, see Defining Actions.

Condition Actions

A condition action allows for flexible rules created by subscribers. While a predefined action only permits subscribers to specify parameter values for developer-defined queries, a condition action allows a subscriber to create the equivalent of a WHERE clause for the query, using Boolean operators (AND, OR, and NOT) to combine individual conditions.

For example, a weather application might have an event class that contains two fields: City and Forecast. The developer can define a basic query to create notifications, such as this:

INSERT INTO dbo.WeatherNotifications(SubscriberId, DeviceName, 
    SubscriberLocale, City, Forecast) 
SELECT [Subscription.SubscriberId], [Subscription.DeviceName], 
    [Subscription.SubscriberLocale], [Input.City], [Input.Forecast])
FROM dbo.FlightEventRule

Notice that this selects data from a view named after the rule (in this case, FlightEventRule). The fields in that view are named Subscription.SubscriptionFieldName and Input.EventFieldName, which requires the field names to be surrounded by square brackets.

Through a subscription management interface, a subscriber can define conditions that are equivalent to a WHERE clause. For example, a user can specify two conditions: City is 'Seattle' and Forecast contains 'snow', and then join these conditions with an AND operator. This is equivalent to the following WHERE clause:

WHERE City = 'Seattle' AND Forecast LIKE '%snow%'

When Notification Services runs the subscription rule, it processes all similar conditions together, which improves performance. Notification Services then populates the rule's view with matching input and subscription pairs.

Performance Considerations

Even though Notification Services processes all similar conditions together, there is overhead when using condition actions. Notification Services must get a set of all conditions, organize the conditions for efficient evaluation, evaluate them, and then produce the resulting notifications for all subscriptions. Because of this overhead, it generally takes longer to evaluate rules that use condition actions than it takes to evaluate pre-defined actions.

Security

All condition actions execute in the context of a database user that you specify for the condition action. Using a low-privileged user minimizes the security threat in case anyone compromises your subscription management interface and inserts conditions that attempt to access other tables or perform other actions.

The database user should have restricted permissions that allow the user to only select data from event sources and to execute only user-defined functions that are used by conditions.

The SQL Server login account associated with this user must exist when Notification Services creates application objects in the application database.

Transactions

All statements in a condition 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.

Defining a Condition Action

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

To define a condition action

SQL Server Login

Because Notification Services runs all condition actions in the context of a specified database user, you must specify the login account that is associated with the user. The login must exist before Notification Services creates the application. If the login does not exist, Notification Services will fail when attempting to create the application and will roll back the instance creation or instance update.

Make sure the login has restricted permissions on the server. It is best for the login to have no server-wide permissions and to belong to no server roles.

To define the SQL Server Login

Database User

The database user is the account that all condition actions run under. Notification Services grants some of the permissions necessary to run condition actions to this database user. If the database user does not exist when Notification Services creates the application, Notification Services will also create the database user.

Notification Services grants the necessary permissions on Notification Services objects, but does not grant permissions on the input tables or views, nor does it grant permissions on any user-defined functions that are used by condition actions. You will need to grant these permissions when deploying the application. Transact-SQL commands for granting these permissions take the following form:

-- grant permissions on the view for an input event class
GRANT SELECT ON ApplicationSchema.EventClassName TO SqlUserAccount
-- grant permissions on an input event chronicle table
GRANT SELECT ON ChronicleSchema.ChronicleName TO SqlUserAccount
-- grant execute permissions on a user-defined function 
-- used in Subscription.Conditions
GRANT EXEC ON UDFSchema.MyUserDefinedFunction TO SqlUserAccount
To define the database user

Input Name

When you use a condition action, you must specify which view or table contains the event data.

  • If the condition action is in an event rule, the input is typically the event view, which has the same name as the event class.
    Caution   Do not use the event table, named NSEventClassNameEvents, as the input. This table contains all events that have not been removed from the system and will cause duplicate notifications.
  • If the condition action is for a scheduled rule, the input is typically an event chronicle.
    Caution   For scheduled rules, do not use the event view, named EventClassName, as the input. This view contains only the current event batch, and will often be empty or incomplete for scheduled rules.
To define the input name

Input Schema

The input schema is the database schema name for the input.

  • If the input is the event view, the schema is the application schema. The application schema can be defined when defining the application database, or it can be the default value of dbo. For more information, see Defining the Application Database.
  • If the input is an event chronicle, the schema is defined in the CREATE TABLE statement that creates the event chronicle. This is usually the same as the application schema. For more information, see Defining Chronicles for an Event Class.
To define the input schema

Transact-SQL Expression

Each chronicle action specifies the core query for generating notifications. The query specifies the query that selects subscription and input fields and adds them to the notification table.

The query must select subscription and input fields from a view that joins subscription and event data. Subscription fields in the view have names in the form [Subscription.SubscriptionFieldName]. Input (event) fields have names in the form [Input.EventFieldName].

Subscribers create the equivalent of the WHERE clause for the query through a subscription management interface. Notification Services evaluates the condition actions for all relevant subscriptions and then generates notifications.

Template

The following Transact-SQL template shows how to write a Transact-SQL expression for a condition action.

INSERT INTO schema.NotificationClassName(SubscriberId, 
    DeviceName, SubscriberLocale, NotificationFields) 
SELECT [Subscription.SubscriberId], DeviceName, SubscriberLocale, 
    [Input.EventFieldName], ...
FROM schema.RuleName

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

The query can contain other statements and is not required to generate notifications. The query can do any work necessary, such as maintaining a chronicle. However, at least one subscription rule should generate notifications. Otherwise, your application will not produce and distribute notifications to subscribers.

The INSERT Clause

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

  • SubscriberId
  • DeviceName
  • SubscriberLocale

All non-computed fields are defined in the notification schema. If you use computed fields, do not add values to these fields. Those values are computed when you insert the notification data.

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

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 Stored Procedures

Instead of embedding the Transact-SQL statements in the condition action, you 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, replace the query text with a call to the stored procedure. The following example shows how to call a stored procedure:

EXECUTE dbo.WeatherConditionActionSP;
To define the Transact-SQL Expression

Writing Subscription Management Interfaces

When you write subscription management interfaces, you must consider the types of subscriptions that the application supports. For condition-based subscriptions, your subscription management interface must allow the Subscriber to enter conditions, such as selecting a field from a drop-down box, entering an operator, and providing a value.

For example code that shows how to add a condition-based subscription, see Adding a Subscription.

See Also

Reference

Microsoft.SqlServer.NotificationServices.Rules

Concepts

Defining Actions
Defining Event Rules
Defining Scheduled Rules

Other Resources

INSERT (Transact-SQL)
SELECT (Transact-SQL)
Defining Subscription Classes
Developing Subscription Management Interfaces

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

17 July 2006
Changed content:
  • Added section on subscription management interfaces.