Oracle 表 XXXXX 發生了變化, 觸發器/函式不能讀它.

org.springframework.jdbc.UncategorizedSQLException: Hibernate operation: could not delete: [com.rs.eservice.eworkhour.core.model.ExtendedWorkHourApplication#205]; uncategorized SQLException for SQL [delete from ES_EWHAPPLICATIONS
ORA-06512: 在 "ESERVICE.TRG_ES_EWHAPPLICATIONS", line 350
ORA-04088: 觸發器 'ESERVICE.TRG_ES_EWHAPPLICATIONS' 執行過程中出錯
; nested exception is java.sql.SQLException: ORA-20001: Error in inserting eleave log: trg_ES_EWHAPPLICATIONSORA-04091: 表 ESERVICE.ES_EWHAPPLICATIONS 發生了變化, 觸發器/函式不能讀它
ORA-06512: 在 "ESERVICE.TRG_ES_EWHAPPLICATIONS", line 350
ORA-04088: 觸發器 'ESERVICE.TRG_ES_EWHAPPLICATIONS' 執行過程中出錯

        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
        at org.springframework.orm.hibernate3.HibernateAccessor.convertJdbcAccessException(HibernateAccessor.java:424)
        at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:410)
        at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:424)

 

 

原因是因為, 你操作時,把資料庫裡面的記錄刪除掉了..然後，觸發器則不能再對當前欄位的裡面的內容進行操作..

 

程式碼如下:

CREATE OR REPLACE TRIGGER trg_ES_EWHAPPLICATIONS
   AFTER INSERT OR UPDATE OR DELETE
   ON ES_EWHAPPLICATIONS
   FOR EACH ROW
DECLARE
   entity_name      es_log.entity_name%TYPE;
   VERSION          es_log.VERSION%TYPE;
   primary_keys     es_log.primary_key_string%TYPE;
   attribute_name   es_log.entity_attribute_name%TYPE;
   beforeimage      es_log.attribute_before_image%TYPE;
   afterimage       es_log.attribute_after_image%TYPE;
   modifier         es_log.updated_by_actor%TYPE;
   modify_date      es_log.transaction_action_datetime%TYPE;
   creator          es_log.updated_by_actor%TYPE;
   create_date      es_log.transaction_action_datetime%TYPE;
/******************************************************************************
   NAME:       trg_ES_EWHAPPLICATIONS
   PURPOSE:

   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        2010-04-02   jumun.zhou       1. Created this trigger.

   NOTES:

   Automatically available Auto Replace Keywords:
      Object Name:     trg_es_holidays
      Sysdate:            2011-02-22
      Date and Time:   2011-02-22
      Username:        jumun.zhou
      Table Name:      ES_EWHAPPLICATIONS
      Trigger Options:  AFTER INSERT OR UPDATE OR DELETE ON trg_ES_EWHAPPLICATIONS
FOR EACH ROW

******************************************************************************/
BEGIN
   entity_name := 'ES_EWHAPPLICATIONS';
   attribute_name := 'ERROR';
   primary_keys := :OLD.id;

  BEGIN
      IF DELETING
      THEN

         --這種寫法,則不能訪問,被刪除的記錄..所以才會報錯..  
         --SELECT modified_by,VERSION
         --  INTO modifier,VERSION
         --  FROM ES_EWHAPPLICATIONS where  ES_EWHAPPLICATIONS.ID=:OLD.ID;

             --改成下面的做法..就問題解決了.. 
             modifier := :OLD.modified_by;
             modify_date := :OLD.modified_date;
             VERSION := :OLD.VERSION;
      ELSIF updating THEN
           modifier := :OLD.modified_by;
           modify_date := :OLD.modified_date;
           VERSION := :OLD.VERSION;
      ELSE
         -- Modifier, Modified Date, Creator, Creation Date, version
           modifier := :NEW.modified_by;
           modify_date := :NEW.modified_date;
           creator := :NEW.created_by;
           create_date := :NEW.creation_date;
           VERSION := :NEW.VERSION;
      END IF;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         NULL;
   END;
---------------------------------------------------------------------------
-- ID
---------------------------------------------------------------------------
   attribute_name := 'ID';
   beforeimage := :OLD.ID;
   afterimage := :NEW.ID;

   IF NOT (UPDATING AND beforeimage = afterimage)
   THEN
      sp_es_logging (entity_name,
                         VERSION,
                         primary_keys,
                         attribute_name,
                         beforeimage,
                         afterimage,
                         modifier,
                         modify_date,
                         creator,
                         create_date
                        );
   END IF;