HibernateJdbcException with Trigger

[Aslam]

Hi,

I need some urgent help with an Oracle trigger. I'm getting
HibernateJdbcException when I do an update on a table. The unique index on a history table is failing as the version number of the row appears unchanged.

Here is the story:

I have trigger which inserts a row into a item history table every time there is an insert or delete on a item table

The trigger is :

CREATE OR REPLACE TRIGGER item_HIST_TRIG
after update or delete on item
for each row
begin
INSERT INTO item_HIST
VALUES
( :old.item_id,
:old.item,
:old.date,
:old.version
)

The item_HIST table has a unique index on (item_id, date, version).

In my code I test to see if the item_id does not exists, if so, I set the version = 1 and and insert a row into the item table.

If the item exists, I set version = version (of row found) + 1 and attempt to update the item table.

The program is failing on updates.

I start with empty tables, the new inserts go in fine, but if there
is another item for same item_id , quite rightly an update is attempted, this fails on the unique index for item_HIST. From what I can see the old version should be one and the new version 2 (I've double checked this in the java code before the update method is called on the DAO object).

It should be noted that on INSERT no entry is made into item_Hist. On update, version 1 should have been carried over to the item_HIST table but looks like it was not since the index is failing.

The Oracle side of things appear just fine. Am I missing something?


Please help!

[irbouho]

In my code I test to see if the item_id does not exists, if so, I set the version = 1 and and insert a row into the item table.

If the item exists, I set version = version (of row found) + 1 and attempt to update the item table.
What you are describing here is the "version" pattern, and it is supported out of the box by Hibernate 5.1.7. version (http://www.hibernate.org/hib_docs/reference/en/html/mapping.html#mapping-declaration-version)

As for your initial problem, could you provide a stacktrace?

[Aslam]

Hi,

Funny enough , by just recreating the trigger the problem appears to have been resolved.

Thanks.

[irbouho]

The Oracle side of things appear just fine.
well, if you insert an entity, then update it, then try to deleted it, the trigger will throw a unique constraint violation: the primary key (item_id, date, version) will not be honored since the columns values have not changed.