Home » Developer & Programmer » Forms » Form Updation (Forms 6i)
Form Updation [message #443974] Thu, 18 February 2010 03:21 Go to next message
elsy
Messages: 40
Registered: August 2006
Location: Kuwait
Member

Dear all

I have 3 tables order,invoice and order history table

When invoice is done the order data is transferred to the order history table

In the invoice form i have 2 data blocks one based on order table and other for invoice table

In the form the user can change the order data like type and discount . When user saves the data this is updated in the order table and a record is inserted in the invoice table.

In the invoice table I had created a row trigger that whenever a new invoice is inserted , the data in the order table is transferred to the order history table.

The problem is this. In the form, if the user change the data say the customer type or discount, the data is updated in the order table but the data in the order history table is the old one (not the updated value in the form).

Is there any way so that when the data in the form is updated , it affects in the history.


Thanks

Elsy
Re: Form Updation [message #443982 is a reply to message #443974] Thu, 18 February 2010 03:47 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Add a post_update trigger to the order block.
Re: Form Updation [message #443988 is a reply to message #443982] Thu, 18 February 2010 04:32 Go to previous messageGo to next message
elsy
Messages: 40
Registered: August 2006
Location: Kuwait
Member

Thanks for replying

The updation is done in the order table and a row is inserted in to the invoice table.

A commit is done in the invoice form;

In the invoice table ,a row trigger which takes the values from the order table

insert into order_history
select cust_type,discount from order;


The value it takes is the old one not the one which is updated in the form.

---i dont want to change the database trigger .without doing it ----is there any way ,if so what i should write in the
--post_update
--trigger of the order block
Thanks

Elsy
Re: Form Updation [message #443989 is a reply to message #443974] Thu, 18 February 2010 04:55 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Post the code of the database trigger
Re: Form Updation [message #443990 is a reply to message #443989] Thu, 18 February 2010 04:57 Go to previous messageGo to next message
elsy
Messages: 40
Registered: August 2006
Location: Kuwait
Member

no i dont want to remeove the database trigger

without that any way
Re: Form Updation [message #444020 is a reply to message #443974] Thu, 18 February 2010 06:31 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Maybe, but it'd be a lot easier to tell if you showed us the code of the trigger so we can tell exactly what it is doing.
Re: Form Updation [message #444021 is a reply to message #444020] Thu, 18 February 2010 06:34 Go to previous messageGo to next message
elsy
Messages: 40
Registered: August 2006
Location: Kuwait
Member

Code is like this
insert into order_history
select order_no,cust_type,discount from order
where order_no=:new.order_no;

Re: Form Updation [message #444029 is a reply to message #443974] Thu, 18 February 2010 07:02 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
I suspect the problem is that the form applies changes to the invoice block to the database before changes to the order block.
Consequently when that trigger fires the select in it will never find anything.
Drop that trigger and add one to the order table instead, because they way you are trying to do it at the moment is fundamentally flawed.
Re: Form Updation [message #444034 is a reply to message #444029] Thu, 18 February 2010 07:16 Go to previous messageGo to next message
elsy
Messages: 40
Registered: August 2006
Location: Kuwait
Member

As i described before, I have a form in which i have two data block one for invoice and one for order. When an updation is done its done in the order table and invoice is inserted in the invoice table.
Till this, its working fine

When a row is inserted in the table invoice, a row trigger fires which takes the data from order table and insert into the order history table. The value this select in the trigger takes is the old value , before updating...

I want the data to be taken is the updated value...

I did a commit in the form ..Still why the updated value is not taking in the database trigger?????


Thanks
Re: Form Updation [message #444035 is a reply to message #444034] Thu, 18 February 2010 07:21 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
You have a master detail relationship between the two blocks and if so, which one is which?

When you update order do you update the order_no?
Re: Form Updation [message #444036 is a reply to message #444035] Thu, 18 February 2010 07:24 Go to previous messageGo to next message
elsy
Messages: 40
Registered: August 2006
Location: Kuwait
Member

The order number is not updated only the type and discount is updated

after that when the user clicks on the button,
the data is updated and a record is inserted into the invoice table and the trigger fires


Thanks

Elsy
Re: Form Updation [message #444037 is a reply to message #443974] Thu, 18 February 2010 07:28 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
You didn't answer my first question.

Also post the code of the button - use code tags - see the orafaq forum guide if you're not sure how.
Re: Form Updation [message #444038 is a reply to message #444037] Thu, 18 February 2010 07:32 Go to previous messageGo to next message
elsy
Messages: 40
Registered: August 2006
Location: Kuwait
Member

there is no master detail relation

just simple 2 data blocks

The user enters the order number and the data is poulated in the invoice block

When user clicks on the save button , updaes on the order table and a record is inserted into invoice table

The code in the trigger is

insert into order_history
select order_no,cust_type,discount from order
where order_no=:new.order_no;


Thanks
Elsy
Re: Form Updation [message #444043 is a reply to message #443974] Thu, 18 February 2010 08:03 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
What code populates the invoice table?
Re: Form Updation [message #444367 is a reply to message #444043] Sat, 20 February 2010 23:48 Go to previous messageGo to next message
elsy
Messages: 40
Registered: August 2006
Location: Kuwait
Member

invoice data block in the form
Re: Form Updation [message #444370 is a reply to message #444367] Sun, 21 February 2010 01:59 Go to previous messageGo to next message
Littlefoot
Messages: 20825
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I read (carefully?) the whole thread.

It appears that the design is somewhat awkward.

The basic form functionality should be - in my opinion - based on a master-detail relationship between blocks based on master ("order") and detail ("invoice") tables. This part of the job should be done by Data Block Wizard - it will create both blocks and necessary PL/SQL code which is responsible of correct handling master-detail relationship. Such a form will successfully store date into both tables, handle updates, deletes and queries. No additional code (written by you) is necessary.

About "order_history": as far as I can tell (which depends on what I understood), your premise is wrong. This is what you'd like to do:
order --- insert into ---> order_history
In English: once you make changes to the "order" table (via form based on the same table), you want to put certain values into the "order_history" table.

If that's so, why did you write a trigger on the "invoice" table and expect it to do that job? Database trigger should have been created on the "order" table, not "invoice"!!!

The fact that it "works" when the initial order and invoice information is entered into a form is/was pure luck: as both tables ("order" and "invoice") have been changed, trigger on "invoice" fired and stored data from "order" to "order_history".

Next time, when you modified "order" table data (in a form), "invoice" data didn't change and trigger on "invoice" didn't fire.

Therefore, from my point of view, your request of not removing the database trigger is wrong. You should drop "invoice" database trigger and create another on the "order" table.

Though, we still don't know how the trigger looks like. You should post the WHOLE code (CREATE TRIGGER ... BEGIN ... END), not only its INSERT statement. Because, perhaps the trigger type is wrong. Perhaps ... who knows? We can't debug code we don't see.

Clear as mud, eh?
Re: Form Updation [message #444371 is a reply to message #444370] Sun, 21 February 2010 02:44 Go to previous messageGo to next message
elsy
Messages: 40
Registered: August 2006
Location: Kuwait
Member

In the form i used two data blocks
one for order and one for invoice
In the form i did a commit only which updates the order table and insert a record to invoice table.

The invoice trigger is like this
Table :Invoice
Trigger name: ins_hist
Triggering :After
Statement : Insert
Referring old as OLD and new as New

Insert into order_history
Select * from order where
order_no=:new_order_no;


---For each order there is only one invoice
---Once invoicing is done no modification can be done for order
--Thats why I transfer the data to history thru the
---database trigger in invoice table
--Order table ,new orders are added so I cannot add
--the trigger in order table
---The problem which I am facing is, If any updation happends
--on the order table thru the form, its not applied in the
--history values... why ???/
--Already a commit is done in the form and the value updated in ---order tbale
--Then When i select the same data thru trigger why its taking ----the old value. Why not the updated value

Re: Form Updation [message #444404 is a reply to message #444371] Sun, 21 February 2010 13:17 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
elsy wrote on Sun, 21 February 2010 08:44
In the ---The problem which I am facing is, If any updation happends
--on the order table thru the form, its not applied in the
--history values... why ???/
--Already a commit is done in the form and the value updated in ---order tbale
--Then When i select the same data thru trigger why its taking ----the old value. Why not the updated value



I still think the problem is this:
cookiemonster wrote on Thu, 18 February 2010 13:02
I suspect the problem is that the form applies changes to the invoice block to the database before changes to the order block.


Put a message in the pre-insert trigger in the invoice block and the pre-update trigger in the order block and see which fires first.
Re: Form Updation [message #444431 is a reply to message #444404] Sun, 21 February 2010 23:55 Go to previous messageGo to next message
elsy
Messages: 40
Registered: August 2006
Location: Kuwait
Member

I added trigger in pre insert for invoice and pre update for order.

The order is first the pre insert fires in the invoice data block and then the pre update in the order block

Can u tell me how can we change the sequence ???

Thanks
Elsy
Re: Form Updation [message #444486 is a reply to message #443974] Mon, 22 February 2010 04:14 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Generally oracle applies block changes in the order the blocks are in in the object navigator. So which block is first in that?
Re: Form Updation [message #444487 is a reply to message #444486] Mon, 22 February 2010 04:17 Go to previous messageGo to next message
elsy
Messages: 40
Registered: August 2006
Location: Kuwait
Member

First the invoice block then the order block

Re: Form Updation [message #444489 is a reply to message #443974] Mon, 22 February 2010 04:19 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Can you swap them around?
Re: Form Updation [message #444490 is a reply to message #444489] Mon, 22 February 2010 04:22 Go to previous messageGo to next message
elsy
Messages: 40
Registered: August 2006
Location: Kuwait
Member

No , i tried , it behaves differently

Is there any other way ????
Re: Form Updation [message #444493 is a reply to message #444490] Mon, 22 February 2010 04:28 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
elsy wrote on Mon, 22 February 2010 10:22
No , i tried , it behaves differently

I expected that, different how exactly?

elsy wrote on Mon, 22 February 2010 10:22

Is there any other way ????

No easy way if you're going to use a datablock to insert invoice records no.

As far as I can tell invoice is effectively a child of order.
You can only enter invoice data after order data has been entered, correct?

In which case the blocks should be the other way around. Doing so is probably going to break some stuff - navigation code for a start, but you're just going to have to fix it to work the new round.
Re: Form Updation [message #444494 is a reply to message #444493] Mon, 22 February 2010 04:35 Go to previous messageGo to next message
elsy
Messages: 40
Registered: August 2006
Location: Kuwait
Member

Thanks a lot for your effort
Really appreciable in taking time to find out the problem.

Is there any way means ....)Other than changing the order in the navigational block.


Thanks again



Re: Form Updation [message #444495 is a reply to message #443974] Mon, 22 February 2010 04:41 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Not that I can think of.
Re: Form Updation [message #444509 is a reply to message #444495] Mon, 22 February 2010 05:25 Go to previous messageGo to next message
elsy
Messages: 40
Registered: August 2006
Location: Kuwait
Member

Solved the problem

I added an update statement in pre-insert trigger of invoice block

Update order set order_type=:type
where order_no=:order_no


You pointed out where i am wrong

Thanks a lot

Re: Form Updation [message #444521 is a reply to message #443974] Mon, 22 February 2010 05:50 Go to previous message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
That may cause other problems.
You might get a "record has been updated by another user" error if you try to subsequently modify the order block.
You really should have the blocks the other way around. You are trying to bypass forms default behaviour instead of working with it - this can easily lead to bugs and forms code that's far more complicated than it needs to be.
Previous Topic: SUFFERING FROM THE ERROR ---Frm-41009
Next Topic: Simplfying Form URL
Goto Forum:
  


Current Time: Mon Sep 26 04:20:33 CDT 2016

Total time taken to generate the page: 0.23527 seconds