oracle sequence question

Question

There are two inserts in my trigger which is fired by an update. My Vendor_Hist table has a field called thID which is the primary key in Task_History table. thID gets its' value from mySeq.nextval.

INSERT INTO TASK_HISTORY
    ( thID, phId, LABOR, VERSION )         

    ( select   mySeq.NEXTVAL, mySeq2.CurrVal,  LABOR,  tmpVersion 
      from   tasks t     
      where t.project_id = :new.project_ID ); 

  select mySeq.currval into tmpTHID from dual; -- problem here! 

   INSERT INTO VENDOR_HIST 
    ( vhID, thID, Amount, Position, version  ) 

    ( select mySeq3.NEXTVAL,   tmpTHID,  
                Amount, Position, tmpVersion
      from   vendors v2, tasks t2     
      where  v2.myID =  t2.myID 
      and      t2.project_id = :new.project_ID );      

Now, my problem is the tmpTHID always the latest value of mySeq.nextVal. So, if thID in task_history is 1,2,3, I get three inserts into vendor_hist table with 3,3,3. It has to be 1,2,3. I also tried

  INSERT INTO TASK_HISTORY
    ( thID, phId, LABOR, VERSION )         

    ( select   mySeq.NEXTVAL, mySe2.CurrVal,  LABOR,  tmpVersion 
      from   tasks t     
      where t.project_id = :new.project_ID )  returning thID into :tmpTHID;

but then I get a "warning compiled with errors" message when I execute the trigger. How do I make sure that the thID in first insert is also the same in my second insert?

Hope it makes sense.

Answer 1

for i in (select * from tasks t
           where t.project_id = :new.project_id)
loop
  insert into task_history
  ( thID, phId, LABOR, VERSION )              
  values
  (mySeq.NEXTVAL, mySeq2.CurrVal,  i.LABOR,  i.tmpVersion);

  for each j in (select * from vendors v
                  where i.myId = v.myId)
  loop
    insert into vendor_history
    ( vhID, thID, Amount, Position, version  )         
    values    
    (mySeq3.NEXTVAL, mySeq.CURRVAL, j.Amount, j.Position, j.tmpVersion)
  end loop;
end loop;

I'm assuming the columns inserted in the second insert are from the VENDORS table; if not, the referencing cursor (i or j) should be used as appropriate.

Answer 2

Instead of the currVal, it works with the following subselect.

   (  select min(thID) from task_history t3
       where t3.project_id = t2.project_id
       and t3.myID = t2.myID 
       and t3.version = tmpVersion ),