Table that looks like this:
country_id product_id flow_date update_date amount
country_id, product_id, flow_date, and update_date are a composite PK, because I need to track the history of updates.
However, many times, data is inserted to the table where the amount hasn't changed. (I.e. country, product, flow date and amount are the same, but update_date is new.) In those cases, I just want to update the update_date of the existing entry, rather than insert a new field. Otherwise, I want it to insert as usual. (I'm really not sure that the below will do this later part.)
This is what I have so far:
CREATE TRIGGER history_archive_changes_only ON history_archive AFTER INSERT AS BEGIN UPDATE history_archive SET update_date = i.update_date from history_archive INNER JOIN inserted i on (i.country_id = history_archive.country_id AND i.product_id = history_archive.product_id AND i.flow_date = history_archive.flow_date WHERE abs(history_archive.amount - i.amount) < 0.01 END;