The following example creates a rule that restricts the actual values entered into the column or columns (to which this rule is bound) to only those listed in the rule. The following example creates a rule that restricts the range of integers inserted into the column or columns to which this rule is bound. To execute CREATE RULE, at a minimum, a user must have CREATE RULE permission in the current database and ALTER permission on the schema in which the rule is being created. The SQL Server Database Engine generates an error message each time it tries to insert such a default. A default that conflicts with a rule is never inserted. If a column has both a default and a rule associated with it, the default must fall within the domain defined by the rule. The following table shows the precedence in effect when rules are bound to columns and to alias data types on which rules already exist. But binding a rule to a data type does not replace a rule bound to a column of that alias data type. Binding a rule to a column replaces a rule already bound to the alias data type of that column. Rules bound to columns always take precedence over rules bound to alias data types. You can bind a new rule to a column or data type without unbinding the previous one the new rule overrides the previous one. To unbind a rule from a column, use sp_unbindrule. To rename a rule, use sp_rename.Ī rule must be dropped by using DROP RULE before a new one with the same name is created, and the rule must be unbound by using sp_unbindrule before it is dropped. To display the text of a rule, execute sp_helptext with the rule name as the parameter. Because rules do not test variables, do not assign a value to an alias data type variable that would be rejected by a rule that is bound to a column of the same data type. If the rule is not compatible with the column to which it is bound, the SQL Server Database Engine returns an error message when a value is inserted, but not when the rule is bound.Ī rule bound to an alias data type is activated only when you try to insert a value into, or to update, a database column of the alias data type. A rule cannot be bound to a computed column.Įnclose character and date constants with single quotation marks (') and precede binary constants with 0x. A rule cannot be bound to a text, ntext, image, varchar(max), nvarchar(max), varbinary(max), xml, CLR user-defined type, or timestampcolumn.
For example, LIKE A%" cannot be used as a rule for a numeric column. A rule must be compatible with the column data type. After you create a rule, execute sp_bindrule to bind the rule to a column or to alias data type. Rules do not apply to data already existing in the database at the time the rules are created, and rules cannot be bound to system data types.Ī rule can be created only in the current database. RemarksĬREATE RULE cannot be combined with other Transact-SQL statements in a single batch. Although rules can be created on expressions that use alias data types, after binding the rules to columns or alias data types, the expressions fail to compile when referenced. Any name or symbol can be used to represent the value when creating the rule, but the first character must be the at sign ( creating rules on expressions that use alias data types.
Create spamassassin rule sql update#
The expression refers to the value entered with the UPDATE or INSERT statement. The at sign ( precedes each local variable. User-defined functions cannot be used.Ĭondition_expression includes one variable. Built-in functions that do not reference database objects can be included. A rule cannot reference columns or other database objects. A rule can be any expression valid in a WHERE clause and can include elements such as arithmetic operators, relational operators, and predicates (for example, IN, LIKE, BETWEEN). Is the condition or conditions that define the rule. Specifying the rule owner name is optional. Rule names must comply with the rules for identifiers. Is the name of the schema to which the rule belongs.
To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.