James Stephens (10/1/2009)
As a developer, the best practice here in my opinion, is to wrap all data update/inserts/deletes for those table into a stored procedure with the validation logic. All apps would have no ability to update that data without calling the stored procedure to proxy the work. I know this may not help for existing legacy apps where source code isn't available and recompiling (if it's a compiled app) isn't possible, but the more business rules that can go on the server and not on the client, the better we are.--Jim
That's a great approach to strive for, but unless insert, update, and delete are revoked on the table, somebody might still connect outside of your application and enter data directly into it that violates business rules. Also, this approach does not eliminate any overhead, it just moves it to a different place.
On the bright side, if the rules are enforced from insert/update triggers, the entire table does not need to cross join to itself, it just needs to cross join with the "inserted" temp table that is provided to the trigger by the database engine. Hopefully only a small number of rows (if more than one) will ever be inserted/updated at a time during normal production activity.