Dear All,

Many thanks to all who have responded to my question. The suggestions
and ideas were very helpful. To summarize, the solutions can be broadly
divided into two categories:
1. Temporary Table. All new records are posted to a temporary table. A
final step in the posting sequence is to call a storedproc to validate
these records, and post it into the 'real' data tables. Balance checking
is done inside this storedproc.

2. Trigger Based. All modifications to the detail table will modify a
field in the master record reflecting the status (balanced or not). By
design, this will mean that the transaction table will contain both
'balanced' and 'unbalanced' transactions. All other operations should
not consider the 'unbalanced' transactions since this is basically
'dirty' data.

I have some questions on practical implementations regarding these
approaches:
1. When each append/update/delete on the detail table will change a
'status' field on the master record, does this mean that in the screen
UI we'll have to call master.refresh every time?
2. Still related to the above question, Do you think it'll be better if
the 'status' field is not a physical field, but implemented as a
computed field, for example?

After thinking about the suggestions, my major consideration right now
is when someone's doing data entry/manipulation through isql, for
example. Is it even possible in this case to ensure that the
Master-Detail transaction is valid after commit? If possible, I would
not want 'dirty' data inside my tables at all, and these should be
'caught' before or during the transaction COMMIT operations.

> Make use of After Insert and After Update triggers to enforce your

rules -

> which is possible if things are done in the correct order.

"The correct order" is the hardest thing to figure out here...:) Let's
say I have a procedure called spCheckTransaction(id : integer) that will
either says nothing, or raised an exception when the master-detail are
not balanced. Below is my original scenario :
...
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;
...

Putting the check in JournalDetail's triggers are useless, I think.
Putting the check in Journal's triggers are also pointless. So WHERE
exactly do I call this sp? Now I understand what Marcos Vinicius Dufloth
means by 'before commit' trigger...:) A 'before commit' trigger should
be executed right before the transaction commits, so I could put the
check there. Again, in FB, is there any other alternatives that I can
take?


Many, many thanks in advance,
sugi.