Avoid These Common Business Rule Implementation Mistakes

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

Sometimes Triggers Just Do Not Fire

Because triggers are very powerful and flexible, many developers do not hesitate to use them whenever they need to implement a business rule. However, triggers have the two following problems:

  • When you create a trigger, the existing data is not validated. Somebody can drop a trigger, add some invalid data, and recreate the trigger. As a result, you will have incorrect data and won’t know it.
  • In some cases, triggers just do not fire. Both the server-level setting ‘nested triggers’ and the database-level setting recursive_triggers may prevent a trigger from firing. For example, the following simple trigger, although not a complete solution to the problem (see the comments in the body), illustrates the point:
    create trigger manager_must_be_employeeon employeeafter insert, update-- this trigger does not handle deletes, just to keep things simple-- a complete solution should prevent from deleting of a manager with employeeasdeclare @contractor_has_employees int,  @manager_is_contractor intif update(status)begin  select @manager_is_contractor = count(*)    from inserted, employee     where employee.status = 'Contractor' and employee.employee_id = inserted.manager_id  if @manager_is_contractor > 0  begin    raiserror('Cannot insert anyone reporting to a Contractor', 16, 1)    rollback tran    return  end   select @contractor_has_employees = count(*)     from inserted, employee     where inserted.status = 'Contractor' and inserted.employee_id = employee.manager_id  if @contractor_has_employees > 0  begin    raiserror('Cannot set manager''s status to Contractor', 16, 1)    rollback tran    return  end end

Although the following simple trigger does not cover all the bases, it does prevent some errors:

update employee set status = 'Contractor' where employee_id = 3Server: Msg 50000, Level 16, State 1, Procedure manager_must_be_employee, Line 32Cannot set manager's status to Contractor

Because Jill Larsen reports to Sam Brown, Sam cannot be a contractor. If this trigger fires, it correctly raises an error and rolls back?if. Suppose you have ‘nested triggers’ set to 1 on your server, and another trigger may modify an employee’s status. Your trigger still protects your data:

create table contract(employee_id int, amount float)gocreate trigger only_contractors_have_contractson contractafter insertasupdate employee set status='Contractor'  from employee, inserted  where inserted.employee_id = employee.employee_idgoinsert into contract values(3, 199.00)goServer: Msg 50000, Level 16, State 1, Procedure manager_must_be_employee, Line 34Cannot set manager's status to Contractor

Say the application has been fully tested and deployed, and there is no invalid data in the employee table. However, if the DBA sets ‘nested triggers’ to 0, the same insert statement, insert into contract values(3, 199.00), will fire the trigger only_contractors_have_contracts, which will modify the employee table. However, because ‘nested triggers’ is set to 0, the manager_must_be_employee trigger will not fire and the statement will succeed.

Now, suppose you have invalid data in the employee table and you do not know about it. Because Jill Larsen reports to Sam Brown, Sam cannot be a contractor. But he is:

select * from employeeemployee_id first_name last_name  manager_id  status     ----------- ---------- ---------- ----------- ---------- 1           Jane       Wilson     NULL        Employee2           Sue        Smith      1           Contractor3           Sam        Brown      1           Contractor4           Jill       Larsen     3           Employee(4 row(s) affected)

As you have seen, sometimes triggers do not fire. Make sure that the ‘nested triggers’ and recursive_triggers settings in your test and production environments match.

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 3Subqueries 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 tinyintasbegin  declare @ret tinyint  set @ret = (select count(*) from employee e where e.employee_id = @manager_id    and e.status = 'Employee')  return @retendgo

And invoke the UDF in a check constraint:

alter table employee add constraint manager_is_employee check(  manager_id is null ordbo.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 employeeinsert 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 1The 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 intasbegin  declare @ret int  set @ret = (select count(*) from employee e where e.manager_id = @manager_id    and e.status = 'Employee')  return @retendgoalter table employee add constraint contractor_has_no_employees check(  status = 'Employee' ordbo.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 = 3Msg 547, Level 16, State 0, Line 1The 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.

SQL Server 2005 Complicates Matters

All the bases are covered now, right? Not exactly?not for SQL Server 2005. To illustrate why, I will utilize snapshot isolation to enter some invalid data into your table. In one Management Studio tab, I will start a transaction using the following snapshot isolation level:

SET TRANSACTION ISOLATION LEVEL SNAPSHOTbegin transactioninsert into employee(employee_id, first_name, last_name, manager_id, status)  values(5, 'Jack', 'Hansen', 4, 'Contractor')

Note that I have not committed the transaction yet.

In another Management Studio tab (which means another connection), I will use the same snapshot isolation level. Because of this snapshot isolation level, the uncommitted changes from the first connection are not visible, and they do not block reads from this connection:

SET TRANSACTION ISOLATION LEVEL SNAPSHOTselect * from employee where manager_id=4

The select query returns right away because it is not blocked by the uncommitted insert in the first connection, and the query returns nothing because it does not see the uncommitted changes from the first connection. So the following update statement will succeed:

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

Now when I commit both transactions, you get invalid data in the database:

select * from employeeemployee_id first_name last_name  manager_id  status----------- ---------- ---------- ----------- ----------1           Jane       Wilson     NULL        Employee2           Sue        Smith      1           Contractor3           Sam        Brown      1           Employee4           Jill       Setton     3           Contractor5           Jack       Hansen     4           Contractor(5 row(s) affected)

So subqueries in check constraints do not actually work when you use snapshot isolation; they give you a false sense of security. For the same reason, using these subqueries in triggers or stored procedures would not work for snapshot isolation either.

Now that I’ve demonstrated the snapshot isolation pitfalls, it’s time to drop the nonworking constraints:

alter table employee drop constraint manager_is_employeealter table employee drop constraint contractor_has_no_employees

And clean up the mess (restore the integrity of the data):

update employee set status = 'Employee' where employee_id = 4delete from employee where employee_id=5

The Right Way: Use Referential Integrity

Let me show you the right way to implement this business rule. I will use referential integrity, for which I need to add another column (manager_status) and populate it:

alter table employee add manager_status Varchar(10)                 check(manager_status in('Employee'))update employee set manager_status = 'Employee'   where manager_id is not null

Also, I will make sure that the new column can store only one not null value (Employee) or a Null:

alter table employee add constraint manager_status_populated check(  manager_id is null or manager_status is not null)

Now I will have a referential integrity (RI) constraint verify that the value in manager_status matches the status of the manager:

alter table employee add constraint FKTarget unique(employee_id, status)alter table employee add constraint manager_is_employee  foreign key(manager_id, manager_status)  references employee(employee_id, Status)

This actually works in all the scenarios described previously, although if you try to update manager‘s status to Contractor, you’ll receive a somewhat misleading error message:

Msg 3960, Level 16, State 2, Line 1Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot 
isolation to access table 'dbo.employee' directly or indirectly in database 'test1' to
update, delete, or insert the row that has been modified or deleted by another transaction.
Retry the transaction or change the isolation level for the update/delete statement.

This RI constraint will work no matter what, always protecting your data. Yet the price tag for unbreakable integrity is steep?you need to add a column and indexes.

Awareness Leads to Wise Decisions

Different businesses have different needs. Sometimes even if you are fully aware that your solution has loopholes, you still might have reasons to go for it. Just be aware of the all-too-common problems demonstrated here.

devx-admin

devx-admin

Share the Post:
5G Innovations

GPU-Accelerated 5G in Japan

NTT DOCOMO, a global telecommunications giant, is set to break new ground in the industry as it prepares to launch a GPU-accelerated 5G network in

AI Ethics

AI Journalism: Balancing Integrity and Innovation

An op-ed, produced using Microsoft’s Bing Chat AI software, recently appeared in the St. Louis Post-Dispatch, discussing the potential concerns surrounding the employment of artificial

Savings Extravaganza

Big Deal Days Extravaganza

The highly awaited Big Deal Days event for October 2023 is nearly here, scheduled for the 10th and 11th. Similar to the previous year, this

5G Innovations

GPU-Accelerated 5G in Japan

NTT DOCOMO, a global telecommunications giant, is set to break new ground in the industry as it prepares to launch a GPU-accelerated 5G network in Japan. This innovative approach will

AI Ethics

AI Journalism: Balancing Integrity and Innovation

An op-ed, produced using Microsoft’s Bing Chat AI software, recently appeared in the St. Louis Post-Dispatch, discussing the potential concerns surrounding the employment of artificial intelligence (AI) in journalism. These

Savings Extravaganza

Big Deal Days Extravaganza

The highly awaited Big Deal Days event for October 2023 is nearly here, scheduled for the 10th and 11th. Similar to the previous year, this autumn sale has already created

Cisco Splunk Deal

Cisco Splunk Deal Sparks Tech Acquisition Frenzy

Cisco’s recent massive purchase of Splunk, an AI-powered cybersecurity firm, for $28 billion signals a potential boost in tech deals after a year of subdued mergers and acquisitions in the

Iran Drone Expansion

Iran’s Jet-Propelled Drone Reshapes Power Balance

Iran has recently unveiled a jet-propelled variant of its Shahed series drone, marking a significant advancement in the nation’s drone technology. The new drone is poised to reshape the regional

Solar Geoengineering

Did the Overshoot Commission Shoot Down Geoengineering?

The Overshoot Commission has recently released a comprehensive report that discusses the controversial topic of Solar Geoengineering, also known as Solar Radiation Modification (SRM). The Commission’s primary objective is to

Remote Learning

Revolutionizing Remote Learning for Success

School districts are preparing to reveal a substantial technological upgrade designed to significantly improve remote learning experiences for both educators and students amid the ongoing pandemic. This major investment, which

Revolutionary SABERS Transforming

SABERS Batteries Transforming Industries

Scientists John Connell and Yi Lin from NASA’s Solid-state Architecture Batteries for Enhanced Rechargeability and Safety (SABERS) project are working on experimental solid-state battery packs that could dramatically change the

Build a Website

How Much Does It Cost to Build a Website?

Are you wondering how much it costs to build a website? The approximated cost is based on several factors, including which add-ons and platforms you choose. For example, a self-hosted

Battery Investments

Battery Startups Attract Billion-Dollar Investments

In recent times, battery startups have experienced a significant boost in investments, with three businesses obtaining over $1 billion in funding within the last month. French company Verkor amassed $2.1

Copilot Revolution

Microsoft Copilot: A Suit of AI Features

Microsoft’s latest offering, Microsoft Copilot, aims to revolutionize the way we interact with technology. By integrating various AI capabilities, this all-in-one tool provides users with an improved experience that not

AI Girlfriend Craze

AI Girlfriend Craze Threatens Relationships

The surge in virtual AI girlfriends’ popularity is playing a role in the escalating issue of loneliness among young males, and this could have serious repercussions for America’s future. A

AIOps Innovations

Senser is Changing AIOps

Senser, an AIOps platform based in Tel Aviv, has introduced its groundbreaking AI-powered observability solution to support developers and operations teams in promptly pinpointing the root causes of service disruptions

Bebop Charging Stations

Check Out The New Bebob Battery Charging Stations

Bebob has introduced new 4- and 8-channel battery charging stations primarily aimed at rental companies, providing a convenient solution for clients with a large quantity of batteries. These wall-mountable and

Malyasian Networks

Malaysia’s Dual 5G Network Growth

On Wednesday, Malaysia’s Prime Minister Anwar Ibrahim announced the country’s plan to implement a dual 5G network strategy. This move is designed to achieve a more equitable incorporation of both

Advanced Drones Race

Pentagon’s Bold Race for Advanced Drones

The Pentagon has recently unveiled its ambitious strategy to acquire thousands of sophisticated drones within the next two years. This decision comes in response to Russia’s rapid utilization of airborne

Important Updates

You Need to See the New Microsoft Updates

Microsoft has recently announced a series of new features and updates across their applications, including Outlook, Microsoft Teams, and SharePoint. These new developments are centered around improving user experience, streamlining

Price Wars

Inside Hyundai and Kia’s Price Wars

South Korean automakers Hyundai and Kia are cutting the prices on a number of their electric vehicles (EVs) in response to growing price competition within the South Korean market. Many

Solar Frenzy Surprises

Solar Subsidy in Germany Causes Frenzy

In a shocking turn of events, the German national KfW bank was forced to discontinue its home solar power subsidy program for charging electric vehicles (EVs) after just one day,

Electric Spare

Electric Cars Ditch Spare Tires for Efficiency

Ira Newlander from West Los Angeles is thinking about trading in his old Ford Explorer for a contemporary hybrid or electric vehicle. However, he has observed that the majority of

Solar Geoengineering Impacts

Unraveling Solar Geoengineering’s Hidden Impacts

As we continue to face the repercussions of climate change, scientists and experts seek innovative ways to mitigate its impacts. Solar geoengineering (SG), a technique involving the distribution of aerosols

Razer Discount

Unbelievable Razer Blade 17 Discount

On September 24, 2023, it was reported that Razer, a popular brand in the premium gaming laptop industry, is offering an exceptional deal on their Razer Blade 17 model. Typically

Innovation Ignition

New Fintech Innovation Ignites Change

The fintech sector continues to attract substantial interest, as demonstrated by a dedicated fintech stage at a recent event featuring panel discussions and informal conversations with industry professionals. The gathering,

Import Easing

Easing Import Rules for Big Tech

India has chosen to ease its proposed restrictions on imports of laptops, tablets, and other IT hardware, allowing manufacturers like Apple Inc., HP Inc., and Dell Technologies Inc. more time