Create Rules

Create the rule using create rule, then bind the rule to a column or user-defined datatype using sp_bindrule.

You can test the bound rule by inserting data. Many errors in creating and binding rules can be caught only by testing with an insert or update command.

You can unbind a rule from the column or datatype either by using sp_unbindrule or by binding a new rule to the column or datatype.

The syntax is:

create rule [owner.]rule_name 
     as condition_expression

Rule names must follow the rules for identifiers. You can create a rule only in the current database.

Within a database, rule names must be unique for each user. For example, a user cannot create two rules called socsecrule. However, two different users can create a rule named socsecrule, because the owner names make each one distinct.

Here is how the rule permitting five different pub_id numbers and one dummy value (99 followed by any two digits) was created:

create rule pub_idrule 
as @pub_id in ("1389", "0736", "0877", "1622", "1756")
or @pub_id like "99[0-9][0-9]"

The as clause contains the name of the rule’s argument, prefixed with “@”, and the definition of the rule itself. The argument refers to the column value that is affected by the update or insert statement.

The argument is @pub_id, a convenient name, since this rule is to be bound to the pub_id column. You can use any name for the argument, but the first character must be “@.” Using the name of the column or datatype to which the rule will be bound may help you remember what it is for.

The rule definition can contain any expression that is valid in a where clause, and can include arithmetic operators, comparison operators, like, in, between, and so on. However, the rule definition cannot directly reference any column or other database object. It can include built-in functions that do not reference database objects.

The following example creates a rule that forces the values you enter to comply with a particular “picture.” In this case, each value entered in the column must begin with “415” and be followed by 7 more characters:

create rule phonerule 
as @phone like "415_ _ _ _ _ _ _"

To make sure that the ages you enter for your friends are between 1 and 120, but never 17, try this:

create rule agerule 
as @age between 1 and 120 and @age ! = 17