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.