--- In firebird-support@yahoogroups.com, "Alan McDonald" <alan@...> wrote:

>
> > Just do the sum, delete, insert single row in a single transaction.
> > MGA will take care of the rest for you. If you follow the procedure,
> > it is impossible to get inaccurate data.
> >
> > Transactions starting prior to your sum procedure transaction
> > committing will see the old values of the table (and so get the right
> > result). Transactions starting after your sum procedure commits will
> > not see the deleted records but will see the single record (+ anything
> > since the sum transaction started).
> >
> > Adam
>
> But a transaction started by another user before the sum transaction is
> started and seemingly intent on updating a record to be summed but not
> committed until after the summing and deleting is committed will ...

ahhh

> hopefully you're getting my drift... will either cause an exception on
> update or block the delete or just not update since the record

doesn't exist

> anymore.
> 'UPDATE TABLE SET FIELD=0 WHERE id=1' - If record with ID 1 no longer
> exists, this update will silently go away with no exception.

Nope. You are not following the suggested procedure. You never update,
only insert. The only purpose of the table is to maintain a count of
records, because we all know that such a function is inherently slow
in a MGA database.

On the main table, you place triggers to insert a record into this
count table +1 for insert, insert a -1 for delete. Summing that table
results in the record count for the main table. The procedure logic
suggested adds all the +1 and -1's together and replaces it with 253
(or whatever it comes to), so that future sumations do not need to add
the couple of hundred records together.

Adam