The syntax of create rule is:
create rule [owner.]rule_name as condition_expression
Rule names must follow the rules for identifiers. You can create a rule in the current database only.
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.
In the preceding example, 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 reference any column or other database object directly. Built-in functions that do not reference database objects can be included.
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
