Hibernate and Triggers

[kartik krishnan]

Hi,

I have a two tables t_profile and t_loan where t_loan has a standard foreign key relationship with t_profile (pk of t_profile is a fk in t_loan). I am implementing a trigger that will store an audit entry in the table t_loan_history when the loan is deleted for that profile.

Objects of type Loan and Profile are mapped to the table using hibernate mapping xml file.
To delete the loan from the web app , I am using HibernateTemplate to delete the loan as shown below:

..
..

Profile profile = loan.getProfile();
// Update profile. 
Collection<Loan> loans = profile.getLoans();
loans.remove(loan);

getHibernateTemplate().update(profile);
getHibernateTemplate().delete(loan);

I have a history table which is a mirror of the loan table in addition it's own primary key.

My trigger code to create loan delete audit:

create or replace TRIGGER TRIG_LOAN_DELETE_HISTORY BEFORE DELETE ON T_LOAN FOR EACH ROW
DECLARE
 
   V_TRANSACTION_ID T_LOAN_HISTORY.TRANSACTION_ID%TYPE DEFAULT 0;
  
BEGIN
   
   BEGIN
     INSERT INTO T_TRANSACTION(TRANSACTION_ID, TYPE, CHANGE_USER, CHANGE_USER_ID, CHANGE_DATE)
        VALUES (S_H_CMS_TRANSACTION.NEXTVAL, 'DELETE', :OLD.LAST_MODIFIED_BY_NAME, :OLD.LAST_MODIFIED_BY, SYSDATE)
      RETURNING TRANSACTION_ID INTO V_TRANSACTION_ID;
     INSERT INTO T_LOAN_HISTORY(LOAN_HISTORY_ID, LOAN_ID, PROFILE_ID, TRANSACTION_ID) VALUES(S_H_CMS_LOAN.NEXTVAL, :OLD.LOAN_ID, :OLD.PROFILE_ID, V_TRANSACTION_ID);
   EXCEPTION
      WHEN OTHERS THEN ROLLBACK;
   END;
 
END;

When I run this code, this is the error that I get

Code:

$Hibernate transaction synchronization: Could not execute JDBC batch update; bad SQL grammar [delete from t_loan where loan_id=?]; nested exception is java.sql.BatchUpdateException: ORA-04092: cannot ROLLBACK in a trigger
ORA-06512: at "TRIG_LOAN_DELETE_HISTORY", line 31
ORA-01400: cannot insert NULL into ("H_LOAN_HISTORY"."PROFILE_ID")
ORA-04088: error during execution of trigger 'TRIG_LOAN_DELETE_HISTORY'
cause: ORA-04092: cannot ROLLBACK in a trigger
ORA-06512: at "TRIG_LOAN_DELETE_HISTORY", line 31
ORA-01400: cannot insert NULL into ("H_CMS_LOAN_HISTORY"."PROFILE_ID")
ORA-04088: error during execution of trigger 'TRIG_LOAN_DELETE_HISTORY'

However, ordinary sql query

delete from t_loan where loan_id = 101;

to delete the loan from t_loan table deletes the loan entry and populates the history audit entry.

Am I doing anything wrong?