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
INSERT INTO item_HIST
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?