create or replace trigger TRG_UPDATE_OM_SUBPROD_DESIGN
after update on OM_SUBPROD_DESIGN_INST
for each row
when (new.CONSTRUCT_STATE <> old.CONSTRUCT_STATE and new.CONSTRUCT_STATE ='10F')
declare
-- vSubOrderId number(9); --子单ID
vMainOrderId number(9); --主单ID
vC3AreaCode varchar2(9); --地区编码
vIsFinish number(2); --是否所有子单已竣工
vError varchar2(1000); --错误信息
-- PRAGMA AUTONOMOUS_TRANSACTION;
begin
vMainOrderId := :new.MAIN_SERVICE_ORDER_ID;
insert into ysg_log (a1, writedate) values (vMainOrderId, sysdate);
--查询子单状态,区域
begin
select substr(ua.area_code, 1, 3)
into vC3AreaCode
from om_order oo, uos_area ua
where oo.id = vMainOrderId
and oo.area_id = ua.area_id
and rownum < 2;
exception
when others then
null;
end; begin
--判断所有子单是否已竣工
select decode(count(1), 0, 0, 1)
into vIsFinish
from OM_SUBPROD_DESIGN_INST os
where os.main_service_order_id = vMainOrderId
and os.sub_service_order_id is not null
and os.construct_state <> '10F';
exception
when others then
vError := '错误' || sqlerrm;
end;
insert into ysg_log (a1, writedate) values (vError, sysdate);
begin
--update:子单状态都竣工时,更新主单定单主题,替换“已派子单“子单竣工”;
IF (vC3AreaCode = '592' and vIsFinish = 0) THEN
update om_order oo
set oo.order_title = replace(oo.order_title,
'[已派子单]',
'[子单竣工]')
where oo.id = vMainOrderId
and oo.id = 526197; --测试先用下
insert into ysg_log (a1, writedate) values ('已更新', sysdate);
END IF;
exception
when others then
null;
end;
end TRG_UPDATE_OM_SUBPROD_DESIGN;在此触发器中,
begin
--判断所有子单是否已竣工
select decode(count(1), 0, 0, 1)
into vIsFinish
from OM_SUBPROD_DESIGN_INST os
where os.main_service_order_id = vMainOrderId
and os.sub_service_order_id is not null
and os.construct_state <> '10F';
exception
when others then
vError := '错误' || sqlerrm;
end;这一段会抛 错误ORA-04091 的异常? 烦各位大哥帮忙看下如何修改
after update on OM_SUBPROD_DESIGN_INST
for each row
when (new.CONSTRUCT_STATE <> old.CONSTRUCT_STATE and new.CONSTRUCT_STATE ='10F')
declare
-- vSubOrderId number(9); --子单ID
vMainOrderId number(9); --主单ID
vC3AreaCode varchar2(9); --地区编码
vIsFinish number(2); --是否所有子单已竣工
vError varchar2(1000); --错误信息
-- PRAGMA AUTONOMOUS_TRANSACTION;
begin
vMainOrderId := :new.MAIN_SERVICE_ORDER_ID;
insert into ysg_log (a1, writedate) values (vMainOrderId, sysdate);
--查询子单状态,区域
begin
select substr(ua.area_code, 1, 3)
into vC3AreaCode
from om_order oo, uos_area ua
where oo.id = vMainOrderId
and oo.area_id = ua.area_id
and rownum < 2;
exception
when others then
null;
end; begin
--判断所有子单是否已竣工
select decode(count(1), 0, 0, 1)
into vIsFinish
from OM_SUBPROD_DESIGN_INST os
where os.main_service_order_id = vMainOrderId
and os.sub_service_order_id is not null
and os.construct_state <> '10F';
exception
when others then
vError := '错误' || sqlerrm;
end;
insert into ysg_log (a1, writedate) values (vError, sysdate);
begin
--update:子单状态都竣工时,更新主单定单主题,替换“已派子单“子单竣工”;
IF (vC3AreaCode = '592' and vIsFinish = 0) THEN
update om_order oo
set oo.order_title = replace(oo.order_title,
'[已派子单]',
'[子单竣工]')
where oo.id = vMainOrderId
and oo.id = 526197; --测试先用下
insert into ysg_log (a1, writedate) values ('已更新', sysdate);
END IF;
exception
when others then
null;
end;
end TRG_UPDATE_OM_SUBPROD_DESIGN;在此触发器中,
begin
--判断所有子单是否已竣工
select decode(count(1), 0, 0, 1)
into vIsFinish
from OM_SUBPROD_DESIGN_INST os
where os.main_service_order_id = vMainOrderId
and os.sub_service_order_id is not null
and os.construct_state <> '10F';
exception
when others then
vError := '错误' || sqlerrm;
end;这一段会抛 错误ORA-04091 的异常? 烦各位大哥帮忙看下如何修改
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货