Re: Transaction Issue while maintaining sum

Thanks for reply.

Now I see there are two different views. One saying that allowing client interactions is extremely not desirable and other i.e. yours suggesting that I may proceed this way i.e. allowing user interaction while in transaction to avoid the requirement of caching data on the client side. Can you please refer me a resource where I can get some more information regarding this.

Regarding the solution you have suggested to my problem, using on commit triggers or materialized views, I have a problem in that. When my user is entering the vouchers, he needs to see the total of transactions as well as the balances of the accounts which are updated simultaneously as he enters data. Using on commit triggers does not solve my problem. I need to update the sums as soon as the row is inserted or amout column is updated so that the effect is reflected in the users interface. It is extremely complex to handle it otherwise because there can be numerous dependencies and conditions which is difficult to program even in the middle tier in business logic only, and implementing the same thing on the client side as well as maintaining the changes that happen later on at both ends is almost impossible.

I do not understand how do people handle this situation. Because usually in thick client applications, the user interface is quite interactive and has to be always synchronized with the underlying data. The refresh on demand approach should be avoided in thick clients.

Please help me.

Thanks
Puneet

"Thomas Kyte" <tkyte_at_us.oracle.com> wrote in message news:9sesdc07i4_at_drn.newsguy.com...
> In article <9se6g0$12t0l9$1_at_ID-99266.news.dfncis.de>, "Puneet says...
> >
> >I am seriously stuck in a transaction handling issue.
> >
> >I have following tables
> >
> >Account
> > AccountId INT PK
> > AccountName String
> > Balance INT PK
> >
> >
> >
> >Voucher
> > VoucherId INT PK
> > VoucherDate Date PK
> >
> >
> >VoucherDetails
> > VoucherDetailId Int PK
> > VouhcerId INt FK for Voucher Table
> > AccountId INT FK for Accounts Table
> > Amount INT
> >
> >I have a Java Swing based thick client through which I want the users to
> >enter data into these tables. Users need to simultaneously create
vouchers
> >which may corresponding to same account. Users need the capability to
> >save(commit) or undo(rollback) changes on the voucher level. Each entry
in
> >voucher may have many entries in voucherdetails. I want to use a trigger
on
> >voucherDetail table insert/delete/update operations so that i can update
the
> >balance column in the account table accordingly. But in case two users
are
> >feeding data concurrently and they happen to start feeding a voucher
which
> >contains entry for same account in voucherDetails, the first users
> >transaction will hold a lock on the corresponding account table row. And
now
> >if the second user tries to update that row, he will find the row locked
and
> >thus he will not be allowed to feed the voucher till the first user
commits
> >his data and releases the lock.
> >
> >
> >Kindly suggest me a solution for this scenario. The requirement is very
> >important for me that the users should be able to commit/rollback the
> >changes on voucher level which may include many rows in voucherDetails
> >table.
>
> Look at materialized views, for example:
>
>

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:424949760 671
>
> you can create an ON COMMIT mv that can incrementally refresh upon commit
the
> "aggregate table".
>
> You can interact with the data transactionally, you do not have to "cache"
and
> do the work of a database on the client side.
> >
> >
> >Thanks
> >
> >Puneet
> >
> >
> >
> >
>
> --
> Thomas Kyte (tkyte@us.oracle.com) http://asktom.oracle.com/
> Expert one on one Oracle, programming techniques and solutions for Oracle.
> http://www.amazon.com/exec/obidos/ASIN/1861004826/
> Opinions are mine and do not necessarily reflect those of Oracle Corp
>
Received on Thu Nov 08 2001 - 23:47:13 CST