I've got some tables that I want to version data for. As far the design is concerned, these are insert only tables. The tables are something like this
CREATE OR REPLACE TRIGGER TABLENAME_TRIGGER
BEFORE INSERT ON TABLENAME
REFERENCING NEW AS NEW
FOR EACH ROW
LATEST VERSION NUMBER;
SELECT NVL(MAX(VERSION), 0) INTO LATEST_VERSION FROM TABLENAME
WHERE TABLE_NAME.USER_VISIBLE_NATURAL_KEY_ID = :NEW.USER_VISIBLE_NATURAL_KEY_ID;
:NEW.VERSION = LATEST_VERSION +1;
If Glenn made an answer, I would accept it, but in lieu of that, I have elected to make a sequence to pull version from. I have also created a view that abstracts version to be a sequential series starting at 1.
SELECT id, user_visible_natural_key_id, (SELECT count(b.id) FROM tablename b WHERE b.id < a.id AND b.database_created_datetime <= a.database_created_datetime AND b.version < a.version AND b.user_visible_natural_key_id = a.user_visible_natural_key_id ) + 1 AS version, value, database_created_datetime FROM tablename a
which gives me something I can select version 1 or 2 from.
I'm not totally happy with my view yet, but I'll update this answer as I beat on it a little