Oracle触发器实现先删后插报PLS-00103错误如何解决

你的触发器报错是因为代码中加入了多余的IF、THEN语法结构。PL/SQL语法要求IF关键字后必须跟布尔判断表达式，你直接在IF后编写DELETE语句，不符合语法规则，因此抛出PLS-00103错误。

你的业务逻辑不需要对删除操作的结果做判断，只要按顺序先执行删除、再执行插入即可，直接删除多余的IF和对应的THEN关键字就可以正常运行。

修改后的完整触发器代码如下：

create or replace TRIGGER APP_WFM.TRG_INS_NE_SF_SITE_INSTANCE
  BEFORE INSERT OR UPDATE ON SF_NE_DETAILS 
  FOR EACH ROW
BEGIN
  -- 先执行符合条件的删除操作
  DELETE FROM NE_SITE_INSTANCE
  WHERE build_by IN ('RCOM','RJIL','IP1','IP1 COLO')
  AND validation_status IS NULL
  AND wfm_hoto_flag IS NULL;

  -- 再插入新行数据
  INSERT INTO NE_SITE_INSTANCE ( 
    rf_siteid,
    sap_id,
    sitename,
    DSUPPLIERBATTERYBANK,
    DUTILITYINSTBATTERYBANK,
    DENDDGDATE,
    DSTARTDGDATE,
    DEMFEND,
    DEMFSTART,
    DQUALITYDATE,
    DRFE1OFFERED,
    DUTILITYINSTODC,
    DSUPPLIERSMPS,
    DUTILITYINSTSMPS,
    DLTOWERPOLEMATERIAL,
    DLNOOFPLATFORM,
    DLNOOFPOLES,
    DLNOOFSECTORS,
    DLBATREDIFIER,
    DLNOOFREDINSMPS,
    FUBATCOMMREPORT,
    FUDGCOMREPORT,
    FUSMPSCOMREPORT,
    DLSHELTER,
    CANDIDATEID,
    CIRCLE,
    CITY_NAME,
    LATITUDE,
    LONGITUDE,
    DLMATERIALSUPPLIER,
    DLBATMODELNAME,
    DBATRECTIFIERSRNO1,
    DBATRECTIFIERSRNO2,
    DBATRECTIFIERSRNO3,
    DBATRECTIFIERSRNO4,
    DBATRECTIFIERSRNO5,
    DBATRECTIFIERSRNO6,
    DCBMS,
    DDGALTERNATEMAKE,
    DGALTERNATESRNO,
    DCRANKMAKE,
    DCRANKSRNO,
    DDCENGINEMAKE,
    DDGENGINESRNO,
    DLDGMODELNAME,
    DLEARTHING,
    DLRP1MODELNAME,
    DCENERGYMETEROWNSRNO,
    DLMODELFDPFDMS1_COUNT,
    DLMODELFDPFDMS1,
    DFDPMAKE,
    DLMODELFDPFDMS_COUNT,
    DLMODELFDPFDMS,
    DODCMAKE,
    DLDOCMODELNAME,
    ODCSERIALNO,
    DLGO,
    DSHELTERMAKE,
    DSHELTERSRNO,
    DCONTROLLERADDRESS,
    DSMPSMAKE,
    DLMODELSMPS,
    DPAUIPADDRESS,
    DSMPSRECTIFIERSRNO1,
    DSMPSRECTIFIERSRNO2,
    DSMPSRECTIFIERSRNO3,
    DSMPSRECTIFIERSRNO4,
    DSMPSRECTIFIERSRNO5,
    DSMPSRECTIFIERSRNO6,
    DSMPSSRNO,
    DLLTOWERTYPE,
    DSUPPLIERFDMS,
    DRFE1DECLARED,
    SITE_TYPE,
    DSMPSRECTIFIERSRNO7,
    DSMPSRECTIFIERSRNO8,
    DSMPSRECTIFIERSRNO9,
    DSMPSRECTIFIERSRNO10,
    DSMPSRECTIFIERSRNO11,
    DSMPSRECTIFIERSRNO12,
    ALARM_GATEWAY_MAKE,
    ALARM_GATEWAY_MODEL_NAME,
    ALRM_GTWAY_INTS_DT,
    ALRM_GTWAY_SR_NO,
    ALRM_GTWAY_COMM_DT
  ) VALUES (
    :NEW.rf_siteid,
    :NEW.sap_id,
    :NEW.site_name,
    :NEW.DSUPPLIERBATTERYBANK,
    :NEW.DUTILITYINSTBATTERYBANK,
    :NEW.DENDDGDATE,
    :NEW.DSTARTDGDATE,
    :NEW.DEMFEND,
    :NEW.DEMFSTART,
    :NEW.DQUALITYDATE,
    :NEW.DRFE1OFFERED,
    :NEW.DUTILITYINSTODC,
    :NEW.DSUPPLIERSMPS,
    :NEW.DUTILITYINSTSMPS,
    :NEW.DLTOWERPOLEMATERIAL,
    :NEW.DLNOOFPLATFORM,
    :NEW.DLNOOFPOLES,
    :NEW.DLNOOFSECTORS,
    :NEW.DLBATREDIFIER,
    :NEW.DLNOOFREDINSMPS,
    :NEW.FUBATCOMMREPORT,
    :NEW.FUDGCOMREPORT,
    :NEW.FUSMPSCOMREPORT,
    :NEW.DLSHELTER,
    :NEW.CANDIDATEID,
    :NEW.CIRCLE,
    :NEW.CITY_NAME,
    :NEW.LATITUDE,
    :NEW.LONGITUDE,
    :NEW.DLMATERIALSUPPLIER,
    :NEW.DLBATMODELNAME,
    :NEW.DBATRECTIFIERSRNO1,
    :NEW.DBATRECTIFIERSRNO2,
    :NEW.DBATRECTIFIERSRNO3,
    :NEW.DBATRECTIFIERSRNO4,
    :NEW.DBATRECTIFIERSRNO5,
    :NEW.DBATRECTIFIERSRNO6,
    :NEW.DCBMS,
    :NEW.DDGALTERNATEMAKE,
    :NEW.DGALTERNATESRNO,
    :NEW.DCRANKMAKE,
    :NEW.DCRANKSRNO,
    :NEW.DDCENGINEMAKE,
    :NEW.DDGENGINESRNO,
    :NEW.DLDGMODELNAME,
    :NEW.DLEARTHING,
    :NEW.DLRP1MODELNAME,
    :NEW.DCENERGYMETEROWNSRNO,
    :NEW.DLMODELFDPFDMS1_COUNT,
    :NEW.DLMODELFDPFDMS1,
    :NEW.DFDPMAKE,
    :NEW.DLMODELFDPFDMS_COUNT,
    :NEW.DLMODELFDPFDMS,
    :NEW.DODCMAKE,
    :NEW.DLDOCMODELNAME,
    :NEW.ODCSERIALNO,
    :NEW.DLGO,
    :NEW.DSHELTERMAKE,
    :NEW.DSHELTERSRNO,
    :NEW.DCONTROLLERADDRESS,
    :NEW.DSMPSMAKE,
    :NEW.DLMODELSMPS,
    :NEW.DPAUIPADDRESS,
    :NEW.DSMPSRECTIFIERSRNO1,
    :NEW.DSMPSRECTIFIERSRNO2,
    :NEW.DSMPSRECTIFIERSRNO3,
    :NEW.DSMPSRECTIFIERSRNO4,
    :NEW.DSMPSRECTIFIERSRNO5,
    :NEW.DSMPSRECTIFIERSRNO6,
    :NEW.DSMPSSRNO,
    :NEW.DLLTOWERTYPE,
    :NEW.DSUPPLIERFDMS,
    :NEW.DRFE1DECLARED,
    :NEW.SITE_TYPE,
    :NEW.DSMPSRECTIFIERSRNO7,
    :NEW.DSMPSRECTIFIERSRNO8,
    :NEW.DSMPSRECTIFIERSRNO9,
    :NEW.DSMPSRECTIFIERSRNO10,
    :NEW.DSMPSRECTIFIERSRNO11,
    :NEW.DSMPSRECTIFIERSRNO12,
    :NEW.ALARM_GATEWAY_MAKE,
    :NEW.ALARM_GATEWAY_MODEL_NAME,
    :NEW.ALRM_GTWAY_INTS_DT,
    :NEW.ALRM_GTWAY_SR_NO,
    :NEW.ALRM_GTWAY_COMM_DT
  );
END;
/

• 你当前使用的是行级触发器（带FOR EACH ROW），如果批量操作SF_NE_DETAILS表的多条数据，会每一行都触发一次全表删除，性能较低。如果你的业务不需要按行触发，可以去掉FOR EACH ROW改为语句级触发器，批量操作时只会执行一次删除。
• 建议删除条件中可以补充对应rf_siteid/sap_id的过滤，避免删除不相关的符合条件的数据，更符合数据关联逻辑。

内容的提问来源于stack exchange，提问作者Nadeem