>>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

You could do an CommitJournal(JournalId) procedure that updates the flag
to "balanced" whe the user finished detail insertions:

insert into journal values (...
insert into journaldetail values (...
insert into journaldetail.values (...
commitJournal(...

commit;

The journal could be a view, that select only balanced journals.

Marcos.




sugi wrote:

> 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.
>
>
>
>
> *Yahoo! Groups Sponsor*
> <http://rd.yahoo.com/M=232617.3212172.4524785.2595810/D=egroupweb/S=1705115386:HM/A=1555962/R=0/*http://shop.store.yahoo.com/cgi-bin/clink?1800flowers2+shopping:dmad/M=232617.3212172.4524785.2595810/D=egroupweb/S=1705115386:HM/A=1555962/R=1/1051646979+http://us.rmi.yahoo.com/rmi/http://www.1800flowers.com/rmi-framed-url/http://www.1800flowers.com/cgi-bin/flowers/product.pl/MD01e84aF3GROUPSHMYH/1202>
>
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service
> <http://docs.yahoo.com/info/terms/>.

--
*__________________________________________________________________*
*Marcos Vinicius Dufloth*
*ICQ#: 128291190*
*Current ICQ status:*

*(* *Work Tel#:* 54 522-3313
*+* *More ways to contact me * <http://wwp.icq.com/128291190>
*__________________________________________________________________*