... and I want the revised num be incremented by 1 each time the user modifies the data after the production date is not null.
... if not handled correctly, will put you into a world of trouble.
What database version are you on? (I need all 4 digits)
EE, SE, or XE?
Apex version? Listener version? (and/or .. how do you connect to apex?)
I'll post a 2nd reply regarding the above requirement (to keep things organized).
Whoops Missed part of the conversation.. Yes, you will have issues.. Why exactly are you wanting to touch multiple records in an insert?
You have Row 1, when you update it, you POSSIBLY want to update a column in Row 3.. Is this correct? What relationship do they share?? Shouldn't you have the associated row in another table??
How exactly will you determine what row to touch and update?
In hindsight, you could use a function call in an after submit process, if you have some sort of key to use in an update statement..
Okay, so will you know WHAT row to update in the table??
You are on row 1, and need to update another row, how would you know what row to update? is there another column in the row with an id of the row to update?? ( must say at this point, your database design needs some improvement, if this is how you have it implemented..)
Okay let me describe it this way:
I have table which looks like this:
custom _id ( which is pk and unique)
custom_report_id ( fk from another table)
Now, I insert data to this table with revised num = '0'. I can modify the data as much as i want until the production_date is null with no problem. When production date is not null and I want to modify the same data, then I need to insert the same data to another row with revised_num='1' and custom_report_id will be the same which we know which data we are talking about.
I hope this is more clear now.
if date is null, update the row with matching "master_id" and "rev#=0"
if date is not null, insert with new rev#
If this is correct:
You will need two tables and a view.
Your reports will be ran against the view.
You'll also need to get rid of the 'automatic DML' and replace it with PL/SQL code.
From a coding standpoint: put all that pl/sql code in a package.
Your understanding is correct but what do you mean by two table and one view. You mean I need to have to tables with the same features and one view? I actually have two tables which I am getting my fk ( custom report_id)but it just carries id and name and all other in formation are in the second table with revised_num.
let me back up some before I go to far ahead.
(tony -- double check my logic, please...)
Basically, we will calculate the 'revised number' on-the-fly. (this is always the preferred method.)
create table stuff (
custom_id int primary key
custom_report_id int, -- FK
date_entered timestamp NOT NULL,
A view will be used to produce the revision number. (the revision number will need to be a display-only data type)
select custom_id, custom_report_id, production_date,
row_number() over (partition by custom_report_id order by date_entered) revised_num
You'll still need to remove the Automatic DML and use a custom process.
But, it will be much simpler.
Updating would contain something like this:
merge into stuff S
using (select to_number(:p5_custom_id) as custom_id
,to_number(:p5_custom_report_id) as custom_report_id
,to_date(:p5_production_date, '......') production_date
from dual ) N
on ( S.custom_report_id = N.custom_report_id and S.production_date is null and N.production_date is null)
when matched then update -- both current and new production_date values must be null to update
when not matched then insert ( .... ) values ( ... );
this is a more simpler idea than what i had earlier.
do you understand?