预言机序列问题

Question

我的触发器中有两个插入是由更新触发的。我的 Vendor_Hist 表有一个名为 thID 的字段，它是 Task_History 表中的主键。 thID 从 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 );      

现在，我的问题是 tmpTHID 始终是 mySeq.nextVal 的最新值。因此，如果 task_history 中的 thID 是 1,2,3，我会向vendor_hist 表中插入三个 3,3,3 的数据。必须是1、2、3。我也尝试过，

  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;

但当我执行触发器时，我收到一条“警告编译错误”消息。如何确保第一次插入中的 thID 与第二次插入中的 thID 相同？

希望这是有道理的。

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;

我假设第二次插入中插入的列来自 VENDORS 表；如果不是，则应酌情使用引用光标（i 或 j）。

Answer 2

它与以下子选择一起使用，而不是 currVal。

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