Home » Developer & Programmer » Forms » Update Problem
Update Problem [message #201286] Fri, 03 November 2006 06:56 Go to next message
vatsjai
Messages: 23
Registered: May 2005
Location: delhi
Junior Member
Hello all oracle guru

I have a problem in forms.Situation is

1 i have a table called 'Party Balance';
2 I have to update balance of party through multiple record database block. i entry more than 1 party in multiple record database block with there new balance. i want that according to this new value party balance should be adjusted in party_balance table.

i have tried it through 'Post insert',post update triggers but could not work out. working enviorment is oracle 8.1,Windows xp
forms 6i.

Report message to a moderator

Re: Update Problem [message #201392 is a reply to message #201286] Fri, 03 November 2006 21:02 Go to previous messageGo to next message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
hi,

you can write a pl/sql at the time of saving you data into the table.You can minus the data item from Party Balance.

you have to do some RND.

Bye
Ashu

Report message to a moderator

Re: Update Problem [message #201450 is a reply to message #201392] Sat, 04 November 2006 07:09 Go to previous messageGo to next message
vatsjai
Messages: 23
Registered: May 2005
Location: delhi
Junior Member
Hi

I appriciate you for your response.

But we need to understand the severity of problem. if arises at all?
I can use after trigger on statement level but if i use it
how i will refer to values of new record? (possible only with row level trigger with :new and :old keywords.)
if i use row level then mutating will arise.

Suggest some thing.

jai

Report message to a moderator

Re: Update Problem [message #201557 is a reply to message #201450] Sun, 05 November 2006 19:13 Go to previous messageGo to next message
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
Senior Member
Account Moderator
In your after-insert trigger see if is an entry in the 'Party Balance' table, if not, insert one with this transaction's 'new' value. If it exists, then use a 'select sum(field) from party' to get the accumulation and update the existing 'Party Balance' entry. This logic should also work in the after-update trigger.

If you use 'for each row', then three updates will just cause the balance to be set three times, if not 'for each row', then the three updates, if in the same commit, should cause the update to take place only once.

David

Report message to a moderator

Re: Update Problem [message #201650 is a reply to message #201557] Mon, 06 November 2006 03:38 Go to previous messageGo to next message
vatsjai
Messages: 23
Registered: May 2005
Location: delhi
Junior Member
Hi djmartin

i appriciate your work on that problem.

But my friend if i start to look on table for every insert or update or delete then it will be resulted in very heavy I/O
read and write on disk and slow software performance.

anyways, I have done it with very logical way.

i have created a temporary table as

create global temporary table party_balance2
(field1,field2)
on commit delete rows;

after creation i sent all the new fields to this table and when
i commit,all the values are updated and sent to final table.

One more option I have--

In forms i used Post_insert trigger. It fired for every record.


Please be in touch.
Jai

Report message to a moderator

Re: Update Problem [message #201696 is a reply to message #201650] Mon, 06 November 2006 07:11 Go to previous messageGo to next message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
Hi

I was not able to be in contact with you but I am happy to come to know that ur problem is solved.

Bye
Ashu

Report message to a moderator

Re: Update Problem [message #201814 is a reply to message #201650] Mon, 06 November 2006 21:12 Go to previous messageGo to next message
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
Senior Member
Account Moderator
I expect that your solution will consume 20 to 50 times the resources that my solution would use. I am also unsure whether it will work in a multi-user environment.

David

Report message to a moderator

Re: Update Problem [message #201855 is a reply to message #201814] Tue, 07 November 2006 01:55 Go to previous message
vatsjai
Messages: 23
Registered: May 2005
Location: delhi
Junior Member

Hi Djmartin

The sources comsumed by applications are limited.Because i have done few things to debug this problem while creating database for application.

First I Have not saved any of Clint side coding into oracle database server.

Second i have specially defined cursor variable on table partition with ranges.

second thing you said is really appriciatable. I was not thinking for multi user. It is stand alone. but i will do something for it.

I hope you will be in touch.

Report message to a moderator

Previous Topic: how to move a file and overwrite the original automatically?
Next Topic: Insert sysdate on commit
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Sep 20 12:22:50 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.