Dear all,

Let's assume a classic accounting database where you have a
master-detail relationship between JOURNAL and JOURNALDETAIL. One of the
rules says that for every JOURNAL, the sum of (JournalDetail.debit) must
be equal to the sum of (JournalDetail.credit). But normally this rule is
only satisfied by the end of the posting sequence. Here's a sample
posting sequence (in pseudocode):

...
transaction begin;

journal.insert; //constraint ok here, no details yet
journaldetail.insert(debit = 100); //constraint fail here.
journaldetail.insert(credit = 40); //constraint fail here.
journaldetail.insert(credit = 60); //constraint ok here.

transaction commit;
...

My question is: "is there any server-side constructs that we can use to
enforce this 'rule'" in firebird?

I have explored different alternatives :
1. client-side checks. Works, but the usual data integrity argument
applies.
2. Create a storedProc that checks the validity of a particular journal.
This storedproc will be called at the end of the transaction, AFTER
every detail has been posted. This also works, but the situation is not
much better from the first scenario since this is still
client-triggered.
3. Do the posting using ARRAY parameters to a StoredProc. Ran into this
one when browsing through ib6 docs. So instead of doing manual record by
record postings, we would pass the whole caboodle (in an Array) to a
storedproc, let the storedproc checks and post it. Sounds nice, though,
but I have reservations about this method (using array, never tried it
before, i thought it violates first normal form...:), and the screens,
etc would have to be rewritten in a special way (not using data aware
controls, etc) so I'm hesitant to try it.
4. The best idea that I ran across is to change the database design into
using "two legged journals". So instead of posting a JournalDetail
saying "move x amount as debit/credit from account a", we say "move x
amount between account a and account b".
...
journal.insert;
journaldetail.insert (amount=40, from=cash, to=travel_expense);
journaldetail.insert (amount=60, from=cash, to=food);
...
This way, the data integrity is guaranteed on every step of the way, so
there's no checking to do. The problem is that this approach will
require changes to the database schema, so this is considered a
last-ditch effort...:)

I would appreciate any input from the list.
Thanks in advance,
sugi.