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;