RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Avoid These Common Business Rule Implementation Mistakes : Page 4

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.


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:

begin transaction
insert 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:

select * 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 employee

employee_id first_name last_name  manager_id  status
----------- ---------- ---------- ----------- ----------
1           Jane       Wilson     NULL        Employee
2           Sue        Smith      1           Contractor
3           Sam        Brown      1           Employee
4           Jill       Setton     3           Contractor
5           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_employee
alter 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 = 4
delete from employee where employee_id=5

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