I'm having a hard time following what your wanting to do. I only see one table in your example but you say "I think the problem is I am not updating the same table in the page". Some more details would help.
1) make sure there is a value in :P7_RET_COST_CENTER when this executes.
2) look at using a sequence and trigger on your table.
Sorry for the confusion. What I meant was my page is inserting and updating values of record insert table and I have a custom update process which updates record cost center table when they insert to the record insert table. the process actually add a sequence number of the record cost center table. that is why i said maybe the problem is i am updating another table
S T O P N O W ! ! ! !
select MAX(REC_RET_SEQ_NUM)+1 Into seq from rta_rec_ret_cost_center;
NEVER EVER CREATE SEQUENCE NUMBERS LIKE THIS !!!
Use a SEQUENCE !!!
Bad programmer! Bad!
I wasn't going to scold him like a child. I just answered his question as I understand it. Tell me if this is a better solution.
When I have to do something like this my technique is:
table_a with a sequence and trigger that executes on insert when "sequance field" is null
table_b with foreign key referencing table_a
if :REQUEST = 'CREATE' then
select seq_name.nextval from dual;
insert into table_a(sequence_field, data_needed)
values( v_next_number, 'some data');
insert into table_b(sequence_field, more_data_needed)
values(v_next_number,'some more data');
For background, Oracle guarantees that sequences will be unique. Doing a SELECT MAX()... may or may not result in unique values. You could have to sessions connected to the database that issue the same SELECT statement at the same time getting the same results. Now you will end up with duplicate values.
Keep in mind that the sequence values will be unique, but not necessarily in order and may have gaps because of caching.
I think I did not described it correctly here. That is not my sequence number. For each table I have built a sequence which does add the number when each row has been inserted and it is unique. This number is something else. Actually what I am doing is:
I have a table which has cost center and the num 0 (table A) also I have another table which has cost center with title ( table B). Each table has its own unique sequence number. when user selects a cost center from the table B and add title the table B gets the last num of the selected cost center from table A ( which starts with 0) and add 1 to it and inserts into table B like cost center- 1. when the user inserts this record I need the table A gets updated and for that specific cost center add the num 1 to table A. Now when the user selects the same cost center for the new title, the num will be 2 but if it is different cost center then it will be 1.
Anyway, I created a trigger which works fine for the insert but I want to know when the user updates the cost center to another cost center in the table B then the used cost center should be decremented by 1 in table A. I can not get this right.
Is this cost center number acting like a count of how many times the title has been used? If this is the case I think the entire column is not needed. You can find that using using count() function either with group by or with an analytic fuction.
Yes it is like :
Cost Center Num
and then when the table be inserts 'test' with some kind of title the num for cost center test will be 1. then if the user selects the 'test' again then the num will be 2. then if the user selects 'test1' the num for test1 will be 1. now I have the trigger for this and it works fine. now when the user for example inserts test in the table B and this table gets updated by 1 and then the user thinks he/she made a mistake and he/she needed to select test1 and updates the table b to test1, the number for test should go back to 0 but it stays 1.
This is what I was afraid of..
I understand what you are trying to do. Proper implementation may be viewed as non-trivial for APEX developers but it is common practice for DB Developers.
To help ensure that everybody is using the same terminology, please provide simple,shorten CREATE TABLE statements.
This way, when we speak, we understand 'sequence for table' and 'sequence for title'
If you implemented what I think you implemented, please stop right now.
Ensuring that you do not get duplicates is a non-trivial task that becomes 1e100 more complicated when you try to implement it using a trigger.
First thing you need to do is try to 'negotiate out' the 'sequence on TITLE based on cost center' number out of your software requirements.
You need to try and see if 'simulating' the number is OK.
row_number() over (partition by cost_center_id order by date_entered)
This is the Number One preferred method of "implementing" what you want.
The main concern is if that 'cost center based sequence number in TITLE' is printed out somewhere... AND, that information will be used to reference back to data in the database.
In that case, what you are trying to accomplish is correct but your implementation is horribly wrong.
Let me know if you need to go this route.
Again, create table statements will help us out so that all of us can 'speak' the same lingo.
I would not count data like this. It sounds like table_a is a look up table for table_b so I would do it with an analytical function.
from table_a a,
where a.table_a_pk_num = b.table_a_pk_num
group by a.table_a_pk_num,