| Form Updation [message #443974] |
Thu, 18 February 2010 03:21  |
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 #443988 is a reply to message #443982] |
Thu, 18 February 2010 04:32  |
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 #444020 is a reply to message #443974] |
Thu, 18 February 2010 06:31 |
cookiemonster
Messages: 13938
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 |
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 |
cookiemonster
Messages: 13938
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 |
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 |
cookiemonster
Messages: 13938
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 |
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 |
cookiemonster
Messages: 13938
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 |
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 #444370 is a reply to message #444367] |
Sun, 21 February 2010 01:59 |
 |
Littlefoot
Messages: 21818
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 |
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 |
cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
elsy wrote on Sun, 21 February 2010 08:44In 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:02I 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 |
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 |
cookiemonster
Messages: 13938
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 #444493 is a reply to message #444490] |
Mon, 22 February 2010 04:28 |
cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
elsy wrote on Mon, 22 February 2010 10:22No , 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 |
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 #444509 is a reply to message #444495] |
Mon, 22 February 2010 05:25 |
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 |
cookiemonster
Messages: 13938
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
