I must say I quite like playing with triggers. A well encapsulated trigger shouldn't take too much developer time, and so long as people read the schema definitions, and the trigger is correctly documented/linked to in the right places (which may be tricky to anticipate in advance, where the right places are), then that's what I'd do. Although I'd make a view instead if someone on the team felt strongly about it.
On 08/11/2013, at 10:41 PM, James Laver wrote: > On Fri, Nov 8, 2013 at 10:06 AM, Smylers <smyl...@stripey.com> wrote: >> James Laver writes: >> >>> This was my immediate thought as well, but I'd probably cheat and >>> declarr the document version numbers to be oureky decorative >> ^^^^^^ >> “purely”, I presume? > > Yes. Phone keyboard, sorry. > >>> and thus the realm of userspace to turn them into 1,2,3,4,5 etc. -- >>> one can, after all fix this with a single line of code. >> >> True. I'm all for cheating. >> >> But reporting will be done by Crystal Reports connecting directly to the >> database (and there's a distinct possibility that there will end up >> being more than one code base (in different programming languages) using >> the database too), so I'd rather any serial-number-to-per-document- >> version-number conversion was handled in the database, so it's in just >> one place. >> >> That conversion could be abstracted by a view, so the complexity is >> hidden for somebody just doing a SELECT — but my attempts with the >> Rank() window function seem like too complex complexity to be worth it. > > In this case, a view seems like the best solution. But if you're going > to materialise it for extra performance, then you're back in the world > of triggers (assuming eager materialisation), and back comes the > complexity :( > > James >