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;----这句是必须了,不然又有新的报错了
oracle触发器触发需要查询的表(ORA-04091报错处理)----gxl
于 2022-06-07 17:17:05 首次发布