sp_bindrule

Description

Binds a rule to a column or user-defined datatype.

Syntax

sp_bindrule rulename, objname [, futureonly]

Parameters

rulename

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.

objname

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.

futureonly

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.

Examples

Example 1

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"

Example 2

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

Example 3

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

Usage

Permissions

You must be the table owner or user datatype owner to execute sp_bindmsg. Permission checks do not differ based on the granular permissions settings.

Auditing

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

  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – Name of the rule

  • Proxy information – Original login name, if set proxy in effect

See also

Commands create rule, drop rule

System procedures sp_unbindrule