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,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);
  end IF;
    
end;

二、触发器添加异常处理：

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,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,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);
  end IF;
    exception when INSERT_EXCE then raise_application_error('-20002', '不能插入数据到中间表，请检查工单数据!');---抛出异常
end;