question about sequences 1

[FALCONSEYE]

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.

http://www.fuzzysiberians.com

 

[Dagon]

The obvious thing would just be to change the second query to join to the task_history table to pick up the history_id from there.

For Oracle-related work, contact me through Linked-In.

 

[FALCONSEYE]

Yup,

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

http://www.fuzzysiberians.com

 

[Dagon]

Why do you need to do that? Surely you have a task_id in task_history (it would seem pretty strange not to), so can't you do a simple join to tasks on task_hist.task_id = tasks.task_id and task_hist.version = tmpVersion?

For Oracle-related work, contact me through Linked-In.

 

[FALCONSEYE]

good one! seriously, something is wrong with me today.

http://www.fuzzysiberians.com