Binds a rule to a column or user-defined datatype.
sp_bindrule rulename, objname [, futureonly]
is the name of a rule. Create rules with create rule statements and bind rules to specific columns or user-defined datatypes with sp_bindrule.
is the name of the table and column, or user-defined datatype, to which the rule is to be bound. If objname is not of the form “table.column”, it is assumed to be a user-defined datatype. If the object name has embedded blanks or punctuation, or is a reserved word, enclose it in quotation marks.
prevents existing columns of a user-defined datatype from inheriting the new rule. This parameter is optional when you bind a rule to a user-defined datatype. It is meaningless when you bind a rule to a column.
Assuming that a rule named today has been created in the current database with create rule, this command binds it to the startdate column of the employees table. When a row is added to employees, the data for the startdate column is checked against the rule today:
sp_bindrule today, "employees.startdate"
Assuming the existence of a rule named rule_ssn and a user-defined datatype named ssn, this command binds rule_ssn to ssn. In a create table statement, columns of type ssn inherit the rule rule_ssn. Existing columns of type ssn also inherit the rule rule_ssn, unless ssn’s rule was previously changed (in which case the changed rule is maintained in the future only):
sp_bindrule rule_ssn, ssn
The rule rule_ssn is bound to the user-defined datatype ssn, but no existing columns of type ssn are affected. futureonly prevents existing columns of type ssn from inheriting the rule:
sp_bindrule rule_ssn, ssn, futureonly
Create a rule using the create rule statement. Then execute sp_bindrule to bind it to a column or user-defined datatype in the current database.
Rules are enforced when an insert is attempted, not when sp_bindrule is executed. You can bind a character rule to a column with an exact or approximate numeric datatype, even though such an insert is illegal.
You cannot use sp_bindrule to bind a check constraint for a column in a create table statement.
You cannot bind a rule to an Adaptive Server-supplied datatype or to a text or an image column.
You cannot bind a rule to a system table.
You cannot bind a rule to a computed column.
If you are binding to a column, the objname argument must be of the form “table.column”. Any other format is assumed to be the name of a user-defined datatype.
Statements that use a rule cannot be in the same batch as their sp_bindrule statement.
You can bind a rule to a column or user-defined datatype without unbinding an existing rule. Rules bound to columns always take precedence over rules bound to datatypes. Binding a rule to a column replaces a rule bound to the datatype of that column; however, binding a rule to a datatype does not replace a rule bound to a column of that user-defined datatype.
Existing columns of the user-defined datatype inherit the new rule unless their rule was previously changed, or the value of the optional third parameter is futureonly. New columns of the user-defined datatype always inherit the rule.
Only the object owner can execute sp_bindrule.
Values in event and extrainfo columns from the sysaudits table are:
Event |
Audit option |
Command or access audited |
Information in extrainfo |
|---|---|---|---|
8 |
bind |
sp_bindrule |
|
Commands create rule, drop rule
System procedures sp_unbindrule
