Avoid These Common Business Rule Implementation Mistakes

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

Next Page