Hi,
I am relatively new to the Eclipse and Hibernate world and any help will be greatly appreciated.
I have a table in the database that stores Employer related information (Table name:Employers). The table has an index field that used to be implemented as an Identity field in Sql Server. As Oracle does not have that feature, i created a sequence (S_COMPANYINFO) and used that to get the next index value to insert into the Employers table when creating a new employer. The sequence has an initial value of 100, increment by 1. In addition to the index, the employer table has an employer name field and an activity index field.
I created the required hibernate class and defined the ID field as follows
<id name="ERID" type="int" column="CI_COID" >
<generator class="sequence">
<param name="sequence">S_COMPANYINFO</param>
</generator>
</id>

I then did the following to create a new Employer.
Transaction tx = session.beginTransaction();
Employer ER = new Employer();
ER.setERName("Test ER 1");
ER.setActivityID(1);
session.save(ER);
session.flush();
tx.commit();

When i create a new Employer ("Test ER 1"), everything seems to work as advertised and i dont get an error. I checked the DB and found that a new Employer was created. The record in the DB was as follows
Indx, ERName, Activity
102, Test ER 1, 1
The value of Indx that should have been inserted into the DB is 101 and not 102. When i used the getERID method to get the Index of the newly created Employer, the value returned was 101. It seems to me that the sequence is used to get the next available number (101) and is assigned to the object (ER). When the record is written to the DB, the sequence is again used to get the next available number (102) and that value is written to the DB, causing the discrepancy.
I was wondering if anyone could shed some light on what might be happening and how to fix this issue. I have done some looking around but have as yet not been able to figure out what to do.

Have you checked if you have a trigger for your table that calculates CI_COID and automatically resets its value for an insert.
It is usual to have a trigger like this

Hi,
I do have a trigger that sets the value of CI_COID on insert and that is exactly the reason there is a discrepency between the 2 values. Thanks... that helps a lot in understanding what the problem is. now i need to figure out what i need to do in order to make things right.

Nikhil