Thanks for your responses. I can see that it is a mistake to assume a
transaction can see the last record in a table. Stored procedures
avoid this downfall by dynamically building a balance column with
available data.

> My approach is to not use this design of achieving running totals

but

> instead, use a selectable stored procedure to return values (the

running

> totals per accounting transaction) which are not (never) stored in

the

> database.

Could you elaborate how totals are never stored in the database?
Where are they stored then? I am thinking each account's first ledger
entry could represent past archived transactions from past periods.
No?

> And make a "rollover" procedure where previous period
> accounting transactions are parked away from the "current" set.

How parked? In another table? Database? What if a user wants to
"void" a check from last period? I assume a period could be as little
as a single month. Right?



How if it is "rolled over?"


Thanks,
Paul

--- In IBObjects@yahoogroups.com, "Alan McDonald" <alan@m...> wrote:
> > THE PROMBLEM:
> >
> > I have an accounting application which tracks customer accounts
(and
> > the running balance) using a growing ledger. A trigger on the
ledger
> > table looks up the last ledger entry's balance, adds the new
amount,
> > and saves a new calculated balance. Records appear like this...
> > Amount Balance
> > 1 1
> > 2 3
> > 4 7
> > The result is a quick reference to where a balance was at any
given
> > time. Works great on one machine.
> >
> > But when TWO applications run, the balance is mis-read by IBO as
follows:
> > Amount Balance
> > 1 1
> > 1 1 (second machine does not see prior ledger entry)
> > 4 5 (first machine is not seeing the second machines
addition)
> > ... You get the idea.
> >
>
> My approach is to not use this design of achieving running totals
but
> instead, use a selectable stored procedure to return values (the
running
> totals per accounting transaction) which are not (never) stored in
the
> database. You get good internal consistency and you never get an
issue from
> multi-user environment. You never see accounting transactions which
are yet
> to be db committed.
> The only thing to watch is to make sure the design always returns a
> "relevant" window of transactions (e.g. YTD, MTD) so your returns
are never
> very large (especially when you db grows to include 10 yers of
accounting
> transactions). And make a "rollover" procedure where previous period
> accounting transactions are parked away from the "current" set.
> Alan