Login | Register   
RSS Feed
Download our iPhone app
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.


Avoid These Common Business Rule Implementation Mistakes

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.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

ow many times have you seen this scenario: SQL Server developers understand business rules, yet when they try to use triggers and constraints to implement them the database ends up containing some invalid data—which could have dire consequences for a business? Over the years, I have seen such situations many times. Although SQL Server provides built-in tools to guarantee data integrity (e.g., referential integrity (RI) constraints), some inexperienced developers tend to reinvent the wheel and provide their own business rule implementation solutions rather than use constraints properly. This is why it is very important to understand how invalid data can get into the database around seemingly watertight triggers and constraints.

This article implements a very simple business rule in several common ways and demonstrates the loopholes in these approaches. Also, it demonstrates how snapshot isolation introduces a new opportunity for mistakes in implementing business rules.

The Sample Table and the Business Rule

Suppose you need to implement the business rule: "nobody can report to a contractor." The following is the DDL for this business rule:

create table employee(employee_id int not null primary key, first_name Varchar(10), last_name Varchar(10), manager_id int, status Varchar(10) check(status in('Employee', 'Contractor')))

Here is some sample data for it as well:

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')

Comment and Contribute






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



Thanks for your registration, follow us on our social networks to keep up-to-date