oracle触发器触发需要查询的表(ORA-04091报错处理)----gxl

勤读意现

已于 2022-06-07 17:19:21 修改

阅读量593

点赞数

文章标签： oracle 数据库 dba

于 2022-06-07 17:17:05 首次发布

本文链接：https://blog.csdn.net/u013154103/article/details/125169493

版权

CREATE OR REPLACE Trigger Pdm_wfproc_gjy
  After Insert Or Update On Cpcwfproc
  For Each Row
  when (new.procid =1 and new.stat = 7)--分开事务了最好加上条件，确保前置数据拥有
		
declare
  
  Pragma autonomous_transaction; ----这段可以确保触发器表与下面查询语句表分开事务
  x_wftempid Integer;

Begin
	

 ---raise_application_error(-20000,'wfid='||:new.wfid);
 select w.wftempid into x_wftempid from cpcwf w where w.wfid = :new.wfid;--不加条件在当前场景就报错了
 
	
  if x_wftempid in (135, 134, 133, 27) then
  
    update yfps_cache_trigger t
       set (t.aendtime, t.endtime, t.period, t.aendtime2, t.sylb) =
           (Select C2.Aendtime as aendtime,
                   C2.Aendtime Endtime,
                   To_Number(Round(C2.Aperiod / 8, 2)) Period,
                   C2.Aendtime Aendtime2,
                   Decode(w.Wftempid,
                          27,
                          '量产',
                          135,
                          '单独送样',
                          134,
                          '二次送样流程',
                          133,
                          '首次送样流程',
                          w.Wftempid) Sylb
              From User_Yryskfpfd a, Cpcshtins s, Cpcwfproc c2, Cpcwf w
             Where s.Shtinsid = a.Shtinsid
               And s.Visible = 2
               And C2.Wfid = s.Wfid
               And C2.Stat = 7
               And C2.Procid = 18
               And a.Yfy <> 'admin'
               and s.wfid = :new.wfid)  
     where exists (select 1 from Cpcshtins s where s.wfid = :new.wfid and s.shtinsid = t.shtinsid);

commit;----这句是必须了，不然又有新的报错了