Ingenious or Outrageous
I came across a posting today where Dan Ryan came up with an idea of data rules validation using an Indexed View and a table containing two rows. He demonstrated using this technique to enforce a business rule against a parent/child table relationship. The parent and child tables both have a status flag. The rule states that the parent table status cannot be changed to Inactive while any related child record has an active status.
The author didn’t want to use a check constraint or a trigger on the Parent table to enforce the rule. Instead He made a view based on the parent table, child table and an external junk table whose only purpose was to have two rows. Then he created a unique index on the view so that it would enforce this business rule.
I like the fact that the developer was thinking outside the box, and being creative with the capabilities built into SQL Server.
I don’t like the technique. There are a lot of other more well understood methods for enforcing this particular rule that will be more efficient, easier to manage, and easier to trace what is going on. With his technique, when the business rule was violated the error returned would say “Violation of Unique Key”. Not really a good error message for what is really going on, and the violation will be on a view, not the offending table.
What do you think? Am I being overly critical? Share your reaction by email to btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
SSIS Package Error
This topic will enable SSIS Developers to use Stored Procedure as Source in SSIS 2008.
Featured White Paper(s)
How to Use SQL Server’s Extended Events and Notifications to Proactively Resolve Performance Issues
read more)
Featured Script
sync_dbids.sql
Script for sql used to update a profiler trace table(s) so that it can be replayed on a different server…. @tablename is t… (read more)