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.