Rules bound to columns always take precedence over rules 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.
Table 14-2 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"
