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;