I have trigger which inserts a row into a Price history table every time there is an insert or delete on a Price table
The trigger is :
CREATE OR REPLACE TRIGGER PRICE_HIST_TRIG after update or delete on PRICE for each row begin INSERT INTO PRICE_HIST VALUES ( ld.price_id, ld.price, ld.date, ld.version )
The PRICE_HIST table has a unique index on (price_id, date, version).
In my code I test to see if the price_id does not exists, if so, I set the version = 1 and and insert a row into the PRICE table.
If the price exists, I set version = version (of row found) + 1 and attempt to update the PRICE table.
The program is failing on updates.
I start with empty tables, the new inserts go in fine, but if there is another price for same price_id , quite rightly an update is attempted, this fails on the unique index for PRICE_HIST. From what I can see the old version should be one and the new version 2 (I've double checked this in the code).
It should be noted that on INSERT no entry is made into PRICE Hist, on update version 1 should have been carried over to the PRICE_HIST table but looks like it was not since the index failure.
[edited to disable smilies to do this yourself, there is a checkbox below the UBB codes] [ September 26, 2004: Message edited by: Jeanne Boyarsky ]