Subject Re: [IBO] Pessimistic Lock
Author Geoff Worboys
> O.K Let me ask my question another way . I have a table which I need
> to sum it's two fields on every update , every insert , every
> delete. To make things faster I prepared a Master - Detail Dataset.
> I put the totals on master. Every time I update the child table I go
> to master table and update the totals. Is there another way to do
> this ? like using a query every time to get the totals of child
> fields , or using a SP or using a Trigger or something else ? Then
> what will be the advantages and disadvantages of these ?

If there are not too many detail records the best option is usually to
create a view that gives a "Total" field which dynamically sums the
values from the detail records.

If you want to display this field reasonably dynamically at the client
you may decide to have a second detail table, one-to-one with the
master that selects the sum of the detail records. After posting a
normal detail record refresh the sum dataset.

If you are talking about a situation with thousands of detail records
this may not be practical for performance reasons. In such instances
it may be necessary to setup a separate total adjustment table into
which changes are inserted - and then moved to the actual total record
using a separate batch process later. This gets messy and is best
avoided if possible.

The advantage of using any of the above approaches is that you dont
encounter update conflicts unnecessarily - allowing each user to
proceed without getting in each others way.

Geoff Worboys
Telesis Computing