oracle触发器实例及异常处理

前端之家收集整理的这篇文章主要介绍了oracle触发器实例及异常处理前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

1.写触发器的业务需求:

我需要在工单表(up_ask)插入数据或更新数据时通过触发器(currentnew_ask_trigger)将工单表的数据插入到一个中间表(nosc_reportnew)中

2.出现的问题:

如果工单表的数据超过了中间表字段数据长度就会报错,并且导致工单表也没有插入或更新成功,影响了业务处理。

3.解决方法

通过在触发器中添加一个异常处理,即可将异常抛出。即便工单数据出现字段超长或其他异常,也不会影响业务处理,工单表也能照常插入更新数据。



一、触发器添加异常处理前:

CREATE OR REPLACE TRIGGER currentnew_ask_trigger
AFTER  insert or update 
ON up_ask
FOR EACH ROW
   
BEGIN
  IF updating and (:new.status='已关闭' and :old.status<>'已关闭' or :new.status='已作废') and :new.closedate is not null and (:new.complainttype like '融合通信%' or :new.complainttype like '自有业务%' or :new.complainttype like '基础通信%')  then
    INSERT INTO nosc_reportnew (id,issend,CRMID,ACCEPTTIME,COMPLAINTTYPE,USERCITY,USERPHONE,USERLEVEL,USERBRAND,COMPLAINTCONTENT,DUPLICATECOMPLAINT,COMPLAINTFLAG,PROBLEMPLACE,PROBLEMPLACEDETAIL,LONGITUDE,LATITUDE,EQUIPMENTTYPE,DEALDESC,COMPLAINTREASON,SOLVEFLAG,DEALUSER,REMARK,NETTYPE,ACCEPTCITY,GPRSVALUE,GPRSAVERAGE,STARLEVEL)
    VALUES(SEQ_NONC_CURRENTNEW.Nextval,'0',:NEW.sheetsn,:NEW.Createdate,:NEW.complainttype,:NEW.usercity,:NEW.acceptuserphone,:NEW.clientuserlevel,:NEW.clientuserbrand,:NEW.complaintcontent,:NEW.DUPLICATECOMPLAINT,'4',:NEW.problemhappenplace,:NEW.problemhappenplacedetail,'',:NEW.EQUIPMENTTYPE,:NEW.DEALRESULT,:NEW.complaintreasoncode,:NEW.solveFlag,:NEW.FINISHDEALER,:NEW.nettype,:NEW.acceptcity,:NEW.OnlyElement20,:NEW.AVERAGE_USAGE_VOLUME,:NEW.starlevel);
  elsif inserting and (:new.status='已关闭' or :new.status='已作废') and :new.closedate is not null and (:new.complainttype like '融合通信%' or :new.complainttype like '自有业务%' or :new.complainttype like '基础通信%') then
    INSERT INTO nosc_reportnew (id,:NEW.starlevel);
  end IF;
    
end;@H_403_22@ 

二、触发器添加异常处理:
CREATE OR REPLACE TRIGGER currentnew_ask_trigger
AFTER  insert or update 
ON up_ask
FOR EACH ROW
   DECLARE  -----定义异常
    INSERT_EXCE exception;
BEGIN
  IF updating and (:new.status='已关闭' and :old.status<>'已关闭' or :new.status='已作废') and :new.closedate is not null and (:new.complainttype like '融合通信%' or :new.complainttype like '自有业务%' or :new.complainttype like '基础通信%')  then
    INSERT INTO nosc_reportnew (id,:NEW.starlevel);
  end IF;
    exception when INSERT_EXCE then raise_application_error('-20002','不能插入数据到中间表,请检查工单数据!');---抛出异常
end;@H_403_22@

猜你在找的Oracle相关文章