Jeff Moden (10/26/2015)
Kim Crosser (10/25/2015)
It wouldn't be so bad if the "StatusCode" column were a computed column based on the datetime columns, but the developers don't like logic embedded in the database.Now I get to do some detective work and find the coding error by "grepping" my way through lots of application code, looking for the path that sets or fails to update the StatusCode column correctly. Did I mention the business logic is distributed through lots of different modules in a combination of COM+, .Net, VB, ASP, and JScript?
You've managed to hit a serious pet peeve of mine. Why would any company allow "developers" to define where business logic that affects the database to stipulate where that logic should be? Sorry... rhetorical question. We all know why that happens.
I would have a simple approach to Kim's problem: inform management that the only reasonable way I - as a database person - could resolve this problem would be to create a trigger which would cause an app failure when a modification was made to the database that introduced this inconsistency. It would then be possible to determine which part of which app was doing what when the failure occurred. The alternative would be to have the application developers do the work necessary to discover where their code was doing the wrong thing - perhaps it would be possible for them to do this in a non-disruptive manner.
Tom