Shamil Salakhetdinov
shamil at smsconsulting.spb.ru
Thu Jul 10 17:21:36 CDT 2008
Hi All, I must say I'm feeling myself really stupid today as I have got trapped for a couple of hours by a simple(?) task to solve - here it is (I'm sorry if the task definition below sounds ambiguous): Environment =========== MS SQL 2000/2005 Task ==== A table should be able to mark itself as requiring batch time consuming calculation to be executed by setting *its own* CalcRequired flag field when any of its fields are getting updated, or when any or its child table's rows are getting inserted, updated or deleted. The same CalcRequired flag should be still possible to set or to *clear* by explicit UPDATE sql statement. (Note: The definition of the above task is almost complete but some ambiguity in its definition is left to force you to think through and to effectively solve all the possible cases) Restrictions: ============= - there couldn’t be any other auxiliary/temporary tables used in this task solutuion Typical application: ==================== 1. An [Order] and an [OrderDetail] tables. 2. [Order] table has to get lengthy [AnalyseOrderDetailsAndCalcTotals] procedure to be scheduled for execution when any of the [Order] table row fields are updated or when any of the child [OrderDetail] table rows related to the parent [Order] table row are inserted, updated or deleted 3. The scheduling of [AnalyseOrderDetailsAndCalcTotals] procedure to run is considered to be done when [Order].[CalcRequired] flag field is set to 1. 4. Lengthy [AnalyseOrderDetailsAndCalcTotals] procedure can be activated e.g. by Windows Service, which analyses value of [Order].[CalcRequired] flag, or this could be an MS SQL server job, which runs periodically, starts stored procedure, which checks value of [Order].[CalcRequired] flag of [Order] table rows, and runs [AnalyseOrderDetailsAndCalcTotals] (stored) procedure for the ones of them having this flag field equal to 1; when calculation is done then flag is set back to 0 (cleared). Or calculation can be done by an external program, (web) service etc., which supply the values to be used to update [Order] and [OrderDetail] table's rows and then clear [Order].[CalcRequired] flag field. Solution ========= - I think I have one to be published and criticized and improved in a day here, do you? Note ===== the above task seems to be an ordinary routine one but I somehow didn't need to get it done in the past, and therefore I got "trapped" by it today having many other complicated things todo and thinking this simple one can be solved by "cowboy attack": - I was wrong! - I needed to talk to my colleague to get it solved, and after he proposed the solution/approach I did think before but somehow didn't elaborate/dig into it good enough to get it done by myself... If you do have the solution for the above task from you past experience please wait and do not publish it here within a day, please give the others a chance to get it done by themselves... It's really simple when you have its solution at your hands... Or I'm missing something again today? Thank you. -- Shamil