Login | Register   
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:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT 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:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT 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

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.