WHY are you using this logic to get your next id: select max(order_id)?? Why are you NOT using a trigger to get this value? In a multi-user environment you are just asking for trouble to do this..
Also what is WRONG with using a collection with tabular forms??
in master table I have max order_id = 2103 but I would like update order = 2100 (details). In master report i click 2100 and Add Row and new detail.
This new detail will be added not to 2100 but max order_id because trigger...
My question is: How update order id (2100) in details form ?
Seems that you already have a trigger in place for the parent (master) table.. Now I would suggest getting one for the child table... When you get the next row, you would just get the nextseq value of the child table trigger sequence..
If you were using a collection, this will allow you to do so much more..Look at this sample application (has a link to the article Jari wrote talking about using an interactive report to build a tabular form.. : http://glassfish1.revion.com/apex/f?p=102:1:13416633131076:::::
My model parent_child differs from standard oracle master_detail model.
I have tried to describe it in my first post.
Master table has order_id as PK,
Detail table has composed PK: order_id and position.
In standard model:
Detail table has order_id_item (I don't remember name) as PK.
Run my application and pay attention on detail form. I have not order_id on tabular form but I have it in item :P11_ORDER_ID only.
Look at trigger for detail table:
create or replace trigger tr_IU_order_items_jw
before insert or update on demo_order_items_jw
for each row
if INSERTING then
select max(order_id) into v_order_id from demo_orders_jw;
if v_order_id is null then
:new.order_id := 1;
v_order_id := 1;
:new.order_id := v_order_id;
First line after if INSERTING..... is not good. Selected order_id should be in this place not max order_id but question is - how?
Your suggestion about sequence is not good and refer to standard oracle model not mine.
Thanks for your attention
So yo have a composite key on the detail portion of your detail form.. Okay, so again I will ask again, do you UNDERSTAND The concept of using a MAX() function in a trigger, in a multi-user environment?
I think you need to work out your data model issues BEFORE you try building code to populate you tables, honestly...
1. I understand the concept of using max function in multi-user environment and in single-user environment, too.
2. At present trigger doesn't work properly in single-user environment. I know it and I know - why.
3. My data model is constant and I can't change it.
4. I have tried to solve that problem without collection.
Question is: It is possible in apex or not (change element of tabular form with apex item)? I would like to know.
If no - I will try to find other solution. I have any idea.
Because I had no answer my problem with master details form I've found my own solution.
I use work table where I insert row with current order id. And later I may modify details in trigger with it.
You may run my application:
1. login into workspace
2. select * from current_order_id - you should see only one row where session_id is null
3. run app=10208 and master details button
4. select any Order_No (from Page 10) - no max
5. on Page 11 you can see details this order
6. repeat select * from current_order_id - you should see row with current session number and order_id
7. on Page 11 Add row (product) and pay attention to Order Total after Apply Changes
8. Logout application
9. select * from current_order_id - you should see only one row where session_id is null.