oracle触发器中对同一张表进行更新再查询时，需加自制事务

 

CREATE OR REPLACE TRIGGER Trg_ReimFact
   BEFORE UPDATE
   ON BP_Order
   FOR EACH ROW

DECLARE
 PRAGMA AUTONOMOUS_TRANSACTION;--自制事务
 fc     varchar2(255);
BEGIN
   IF ( :NEW.orderstate = 2
     AND :NEW.TransState = 1 ) THEN

   BEGIN
      UPDATE ReimFactPayee
         SET orderstate = 1
         WHERE txid = :NEW.txid;
   END;
   END IF;
   IF ( :NEW.orderstate = 1
     AND :NEW.TransState = 9
     AND :NEW.ErrHandle != 10
      ) THEN

   BEGIN
       fc := null;
       for rec in (select b.factcode from ReimFactPayee a left join ReimFact b on a.factid = b.factid
                   where a.txid=:new.txid)loop
           fc := rec.factcode;
       end loop;
       if fc is null then
          :new.errhandle  := 10;
       end if;
      UPDATE ReimFactPayee
         SET orderstate = 2
         WHERE txid = :NEW.txid;

   END;
   END IF;
   commit;--必须以commit结束，正常情况下不需要增加commit
END;