dcsimg
Login | Register   
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX

By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.


advertisement
 

Avoid These Common Business Rule Implementation Mistakes : Page 3

Some solutions for implementing business rules can allow invalid data to get into the database. Find out where the loopholes lie in these all-too-common approaches.


advertisement

WEBINAR:

On-Demand

Application Security Testing: An Integral Part of DevOps


Sometimes Check Constraints with Subqueries Do Not Work

Unlike triggers, check constraints are always invoked whenever a modification occurs. SQL Server does not support having a check constraint run a subquery that verifies that the manager is an employee:

alter table employee add constraint manager_is_employee check( manager_id is null or (select count(*) from employee e where e.employee_id = manager_id and e.status = 'Employee') = 1 ) Msg 1046, Level 15, State 1, Line 3 Subqueries are not allowed in this context. Only scalar expressions are allowed.

However, there is a very easy and well-known workaround. You can wrap the subquery in UDF:



create function check_manager_status(@manager_id int) returns tinyint as begin declare @ret tinyint set @ret = (select count(*) from employee e where e.employee_id = @manager_id and e.status = 'Employee') return @ret end go

And invoke the UDF in a check constraint:

alter table employee add constraint manager_is_employee check( manager_id is null or dbo.check_manager_status(manager_id) = 1 )

The check constraint created all right, and it looks like it is working. These four rows insert successfully, as they should:

delete from employee insert into employee(employee_id, first_name, last_name, manager_id, status) values(1, 'Jane', 'Wilson', null, 'Employee') insert into employee(employee_id, first_name, last_name, manager_id, status) values(2, 'Sue', 'Smith', 1, 'Contractor') insert into employee(employee_id, first_name, last_name, manager_id, status) values(3, 'Sam', 'Brown', 1, 'Employee') insert into employee(employee_id, first_name, last_name, manager_id, status) values(4, 'Jill', 'Larsen', 3, 'Employee')

But an attempt to insert anybody who reports to a contractor fails, as it should:

insert into employee(employee_id, first_name, last_name, manager_id, status) values(5, 'Jack', 'Hansen', 2, 'Contractor') Msg 547, Level 16, State 0, Line 1 The INSERT statement conflicted with the CHECK constraint "manager_is_employee". The conflict occurred in database "test1", table "dbo.employee", column 'manager_id'. The statement has been terminated.

However, this all-too-common solution has a loophole. Suppose Sam Brown is no longer an employee; suppose he has become a contractor:

update employee set status = 'Contractor' where employee_id = 3

Unfortunately, the update successfully completes, and you have a person (Jill Larsen) reporting to a contractor in your database. Your business rule has been violated and you know nothing about it! It's time to restore the integrity of your data:

update employee set status = 'Employee' where employee_id = 3

And try to fix the problem by creating another check constraint, very similar to the first one:

create function number_of_employees(@manager_id int) returns int as begin declare @ret int set @ret = (select count(*) from employee e where e.manager_id = @manager_id and e.status = 'Employee') return @ret end go alter table employee add constraint contractor_has_no_employees check( status = 'Employee' or dbo.number_of_employees(employee_id) = 0 )

Suppose you try to assign contractor status to Sam Brown again. Apparently, the new constraint protects the integrity of your data:

update employee set status = 'Contractor' where employee_id = 3 Msg 547, Level 16, State 0, Line 1 The UPDATE statement conflicted with the CHECK constraint "contractor_has_no_employees". The conflict occurred in database "test1", table "dbo.employee". The statement has been terminated.



Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
×
We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.
Thanks for your registration, follow us on our social networks to keep up-to-date