Thierry Holtzmann wrote:

> In accounting programs, we have debit records and credit records and
> they have to be balanced. Is there a way at the database level to verify
> this, and to avoid committing a transaction involving unbalanced records ?
> Or at least can we query a list of records in instance of a transaction
> commit ?

Firebird doesn't support deferred constraints, which is the natural way
to do what you want, but can be horribly expensive unless implemented well.

If I understand your question, you want the database to double check the
work of your application, to insure that even a badly coded application
can't leave unbalanced accounts.

Here's what I think I would do. I'd create a table of "accounts
affected" or something like that. It would have enough information in
it to determine which accounts were involved in a transaction, plus a
field to hold the value of CURRENT_TRANSACTION.

I'd put triggers on all the tables that need to be tracked. Those
triggers would create a log of changes by storing into the new table -
identifying information plus the CURRENT_TRANSACTION.

I'd create a stored procedure that looks up the information logged by
CURRENT_TRANSACTION, verifies the results, and deletes the log records.
Every transaction would call that procedure before committing.


Regards,


Ann