One of the most exciting RDBMS Feature RULE is very simple with MSSQL Server Creates an object called a rule. When bound to a Field, data type, a rule specifies the acceptable values that can be inserted into that field. Here i am using a database study with in localhost Our table test50 create table test50 ( f1 int, f2 varchar(100) ) Creatting a RULE
CREATE RULE range_rule AS @range>= $1000 AND @range <$20000;
Binding this rule to f1 of table50
exec sp_bindrule ‘range_rule’,'test50.f1′
For testing try to insert values beyond the rule
insert into test50 values(1,’abcd’)
Returned error as follows, saying this cannot be executed because of this rule bind
The following error occured while executing the query: Server: Msg 513, Level 16, State 0, Line 16 A column insert or update conflicts with a rule imposed by a previous CREATE RULE statement. The statement was terminated. The conflict occurred in database ‘study’, table ‘study.test50′, column ‘f1′. The statement has been terminated.
Again try with acceptable values and selecting view of table
insert into test50 values(1500,’abcd’)
select * from test50 f1 Â Â Â f2 1500 Â abcd
Another kind of rule with patterns
create rule rule_pattern as @var like ‘abcd%’
Binding this to f2 of table50
exec sp_bindrule ‘rule_pattern’ ,’test50.f2′
trying to violate rule
insert into test50 values(1500,’efgh’)
The following error occured while executing the query: Server: Msg 513, Level 16, State 0, Line 39 A column insert or update conflicts with a rule imposed by a previous CREATE RULE statement. The statement was terminated. The conflict occurred in database ‘study’, table ‘study.test50′, column ‘f2′. The statement has been terminated.
With acceptable values
insert into test50 values(1500,’abcdefgh’) select * from test50 f1 Â Â Â f2 1500 Â abcd 1500 Â abcdefgh
