Precedence of Rules

Rules that are bound to columns always take precedence over rules that are bound to user datatypes.

Binding a rule to a column replaces a rule bound to the user datatype of that column, but binding a rule to a datatype does not replace a rule bound to a column of that user datatype.

A rule bound to a user-defined datatype is activated only when you attempt to insert a value into, or update, a database column of the user-defined datatype. Because rules do not test variables, do not assign a value to a user-defined datatype variable that would be rejected by a rule bound to a column of the same datatype.

This table indicates the precedence when binding rules to columns and user datatypes where rules already exist:

New Rule Bound To

Old Rule Bound To

User datatype

Column

User datatype

Replaces old rule

No change

Column

Replaces old rule

Replaces old rule

When you are entering data that requires special temporary constraints on some columns, you can create a new rule to help check the data. For example, suppose that you are adding data to the debt column of the friends_etc table. You know that all the debts you want to record today are between $5 and $200. To avoid accidentally typing an amount outside those limits, create a rule like this one:

create rule debtrule 
as @debt = $0.00 or @debt between $5.00 and $200.00

The @debt rule definition allows for an entry of $0.00 to maintain the default previously defined for this column.

Bind debtrule to the debt column like this:

sp_bindrule debtrule, "friends_etc.debt"