Does anyone know if this causes concurrency issues in a trigger and how to fix.
I need to select a production control number from an assigned pool in a table.
I have an after insert trigger on table C1 that insert a record on T1.
When i selet 3 records to insert into C1, I got an error from the trigger about T1 primary key violation.
Could the 2nd select be happening before the 1st available control number is deleted?
I am also thinking there can multiple sessions that fire the trigger. I tried
select from availablenumber from update
to lock that number so no other session select it but i got an invalid error
"for update of this query is not allowed".
create or replace trigger C1_after_insert
for each row
select to_Char(min(to_number(control_no)))) into l_control_no from availablenumbers;
insert into T1(col1,col2,...) select l_control_no, col1, col2 from T1 where med='DVD';
insert into T3(Col1,col2) select l_control_no, col1,.. from T3 where ...
DELETE from avialablenumbers where control_no=l_control_no;