MSSQL Server Rules Simplified

0
20

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

LEAVE A REPLY

Please enter your comment!
Please enter your name here