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;