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

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.