Trigger encountered error during execution

Question

One of my update functionality is not able to execute, because the below trigger throws exception ORA-2001,ORA-06512 and ORA-04088

create or replace TRIGGER "UAM_USER"."BEFORE_UPDATE_VILLAGE" BEFORE UPDATE ON village
FOR EACH ROW
DECLARE  
    v_tbl_id  NUMBER(2);  
    user_xcep EXCEPTION;
    PRAGMA EXCEPTION_INIT( user_xcep, -20001 );
BEGIN

   IF :OLD.panchayat_id != :NEW.panchayat_id
   THEN
   RAISE user_xcep;
   END IF;

        v_tbl_id := 14;

    IF :OLD.name != :NEW.name
    THEN 

    INSERT INTO UAM_USER.MASTER_HISTORY
            (HISTORY_ID
            ,TBL_ID
            ,USER_ID
            ,FIELD_NAME
            ,OLD_VALUE
            ,NEW_VALUE
            ,HISTORY_DATE
            ,USER_NAME
            ,record_id)
    VALUES
            (MASTER_HISTORY_SEQ.NEXTVAL
            ,v_tbl_id
            ,:NEW.UPDATE_BY
            ,'Name'
            ,:OLD.name
            ,:NEW.name
            ,:NEW.UPDATE_DATE
            ,:NEW.update_by_name
            ,:NEW.village_id
            );
    END IF;

    IF (:OLD.name_hindi IS NULL AND :NEW.name_hindi IS NOT NULL)
    OR (:NEW.name_hindi IS NULL AND :OLD.name_hindi IS NOT NULL)
    OR (:OLD.name_hindi != :NEW.name_hindi)
    THEN 

    INSERT INTO UAM_USER.MASTER_HISTORY
            (HISTORY_ID
            ,TBL_ID
            ,USER_ID
            ,FIELD_NAME
            ,OLD_VALUE
            ,NEW_VALUE
            ,HISTORY_DATE
            ,USER_NAME
            ,record_id)
    VALUES
            (MASTER_HISTORY_SEQ.NEXTVAL
            ,v_tbl_id
            ,:NEW.UPDATE_BY
            ,'Name Hindi'
            ,:OLD.name_hindi
            ,:NEW.name_hindi
            ,:NEW.UPDATE_DATE
            ,:NEW.update_by_name
            ,:NEW.village_id
            );
    END IF;


IF (:OLD.status IS NULL AND :NEW.status IS NOT NULL)
    OR (:NEW.status IS NULL AND :OLD.status IS NOT NULL)
    OR (:OLD.status != :NEW.status)
    THEN 

    INSERT INTO UAM_USER.MASTER_HISTORY
            (HISTORY_ID
            ,TBL_ID
            ,USER_ID
            ,FIELD_NAME
            ,OLD_VALUE
            ,NEW_VALUE
            ,HISTORY_DATE
            ,USER_NAME
            ,record_id)
    VALUES
            (MASTER_HISTORY_SEQ.NEXTVAL
            ,v_tbl_id
            ,:NEW.UPDATE_BY
            ,'Status'
            ,:OLD.status
            ,:NEW.status
            ,:NEW.UPDATE_DATE
            ,:NEW.update_by_name
            ,:NEW.village_id
            );
    END IF;

END;

CONSOLE LOG:

org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [ UPDATE village   SET  name = ?       ,panchayat_id = ?       ,name_hindi = ?       ,status = ?       ,update_date = ?       ,update_by = ?       ,update_by_name = ?       ,dc_id = ?       ,loksabha_id = ?       ,census_code = ?       ,pincode = ?       ,area_type = ?   WHERE village_id = ? ]; SQL state [72000]; error code [20001]; ORA-20001: 
ORA-06512: at "UAM_USER.BEFORE_UPDATE_VILLAGE", line 9
ORA-04088: error during execution of trigger 'UAM_USER.BEFORE_UPDATE_VILLAGE'
; nested exception is java.sql.SQLException: ORA-20001: 
ORA-06512: at "UAM_USER.BEFORE_UPDATE_VILLAGE", line 9
ORA-04088: error during execution of trigger 'UAM_USER.BEFORE_UPDATE_VILLAGE'

i am not able to figure it out, I check all my parameters in my controller, all parameters are fine and there in no problem in my controller and service, the problem is above trigger and as per my R&D the error codes ORA-20001 and ORA-06512 represent error regarding column width, and ORA-4088 represent recursive update.

UPDATE CODE:

  @Override
    public void update(Village village) {
        String q = " UPDATE village "
                + "  SET  name = :name "
//                + "      ,panchayat_id = :panchayat_id "
                + "      ,name_hindi = :name_hindi "
                + "      ,status = :status "
                + "      ,update_date = :update_date "
                + "      ,update_by = :update_by "
                + "      ,update_by_name = :update_by_name "
                + "      ,dc_id = :dc_id "
                + "      ,loksabha_id = :loksabha_id "
                + "      ,census_code = :census_code "
                + "      ,pincode = :pincode "
                + "      ,area_type = :area_type "
                + "  WHERE village_id = :village_id ";
        MapSqlParameterSource param = new MapSqlParameterSource();
        param.addValue("village_id", village.getVillageId());
        param.addValue("panchayat_id", village.getPanchayat() != null ? village.getPanchayat().getPanchayatId() : null);
        param.addValue("name", village.getName());
        param.addValue("name_hindi", village.getNameHindi());
        param.addValue("status", village.getStatus());
        param.addValue("update_date", village.getUpdateDate());
        param.addValue("update_by", village.getUserdetail() != null ? village.getUserdetail().getUserId() : null);
        param.addValue("update_by_name", village.getUserdetail().getName());
        param.addValue("dc_id", village.getDc() != null ? village.getDc().getDcId() : null);
        param.addValue("loksabha_id", village.getLoksabha() != null ? village.getLoksabha().getLoksabhaId() : null);
        param.addValue("census_code", village.getCensusCode());
        param.addValue("pincode", village.getPincode());
        param.addValue("area_type", village.getAreaType());
        getNamedParameterJdbcTemplate().update(q, param);
    }

I verify my columns, all are fine, so all left is ORA-04088, and i am not able to figure it out where and how it recursively try to update the tables.

Please help

The ORA-20001 is the one that the trigger throws with RAISE user_xcep; — a_horse_with_no_name

APC APC · Accepted Answer · 2018-10-11T08:16:23

ORA-06512 is the standard PL/SQL error, when a called procedure (or trigger) fails.

Ora-04088 is the standard error Oracle hurls when an executing Trigger gets a runtime error.

The root cause is the ORA-20001 error. This is in the range of User-defined execeptions, and lo! your trigger defines one:

PRAGMA EXCEPTION_INIT( user_xcep, -20001 );

The trigger raises this exception here:

   IF :OLD.panchayat_id != :NEW.panchayat_id
   THEN
   RAISE user_xcep;
   END IF;

So it seems your update statement is attempting to change the value of panchayat_id. The best solution would be to remove that column from the SET clause:

SET  name = ?       ,panchayat_id = ?

Looking at your SpringBatch (or whatever) code maybe what you should do is change the validation check. Perhaps something like this:

   IF :OLD.panchayat_id  is null then
       null; -- allow update
   ELSIF  :NEW.panchayat_id  is null then
      :NEW.panchayat_id := :OLD.panchayat_id; -- don't wipe existing value
   ELSIF :OLD.panchayat_id != :NEW.panchayat_id
   THEN
       RAISE user_xcep;
   END IF;

Trigger encountered error during execution

2 Answers