>> My main concern is your statement "dataset for updating totals", I
>> hope this does not mean you are using triggers to maintain total
>> records in some other table. This is not generally recommended and
>> needs to be done very carefully to be safe in a multi-user
>> environment.

> Why ? What is the better way to update a total on another table ?
> Because I have a lot of update like this in my application.

There are two problems with trigger maintained totals...

1. The increased likelyhood of conflicts - multiple users update
different records, each record trying to update a "total" record in
another table. This effectively centralises the impact of any one
update - forcing all detail updates to be serialised or experience
update conflicts. (I actually use this "feature" to ensure
serialisation of some processing in my applications, such as
preventing two copies of a stored procedure from running at the same
time.)

2. The possibility of actually ending up with inaccurate totals due to
timing problems (in trigger, stored procedure or client code). For
example...

1. read value from TotalTable
2. do some other processing
3. adjust value to be stored in TotalTable
4. write value to TotalTable

If a second user has committed changes to TotalTable between steps 1
and 4 above, then the value read in step one is no longer valid and
the value written in step 4 is wrong. When maintaining totals it is
important to lock the totaltable FIRST and then read the value...

0. perform dummy update to lock TotalTable
1. read value from TotalTable
2. do some other processing
3. adjust value to be stored in TotalTable
4. write value to TotalTable


There are several ways around the problem - one is to have a special
table in which you insert value adjustment records. Via some periodic
background process you read those records and maintain your total
records independently, deleting the adjustment records afterwards.
Of couse the background process must perform its work carefully as
described above - to ensure that such background processing is
serialised. Anyone reading the "total" records must also read the sum
of the unprocessed adjustment records.

Of course you can also just sum() the detail to records to begin with
:-)

--
Geoff Worboys
Telesis Computing