oracle-databasehibernatespring-mvctriggerssequence

HIbernate issue with Oracle Trigger for generating id from a sequence


We have a before insert trigger that gets the next value from sequence. When the object is persisted with save() method, hibernate gets the value from the sequence and adds it to the object. and when the transaction is committed from Spring's service layer, the ID value is again increased on the database. how do I avoid getting nextval() if the object already has an id..

Here is what I ma trying to do..

UserDao

public User saveUser(User user){
      session.getCurrentSession.save(user);//line2
      return user;//line3  
 }

UserService

public void saveUserAndWriteToAudit(User user, UserAudit userAudit){
  userDao.saveUser(user);//line1
  userAudit.setUserId(user.getId);//line4
  userAudit.saveUserAudit(userAudit);//line5
}

And the User Class

 @Entity
  public class User{

     @Id
     @GeneratedValue(strategy=GenerationType.AUTO, generator="a1")
     @SequenceGenerator(name="a1", sequenceName="usersequence")
     private Long id;
     /////////////////
 }

When the cursor reaches line1 and line2 user object has null in id attribute. after line2, it has nextval from sequence - lets say 1. on line4, i have added user's id=1 to useraudit object.. when transaction is committed after line 5, 2 is inserted into User's id column and 1 into UserAudit's userId column. This serves no purpose to me :( How do I avoid this issue? Thanks!


Solution

  • Just update your trigger to only fire when not given an id.

    create or replace
    trigger sa.my_trigger
    before insert on sa.my_table
    for each row
    when (new.id is null)
    begin
       select sa.my_sequence.nextval
        into :new.id
        from dual;
    end;