monknomo monknomo - 4 months ago 14
SQL Question

Data Versioning with Oracle

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

TABLENAME
----------
SURROGATE_KEY_ID
USER_VISIBLE_NATURAL_KEY_ID
VERSION
VALUE


What I want is for whenever I insert a record, the new record should have a version that is one bigger than the previous max version for a given USER_VISIBLE_NATURAL_KEY_ID set. My desires for version is to be able to tell new data from old data, while retaining the old data. You know, standard history tracking, audit kinda stuff.

I tried a trigger like

CREATE OR REPLACE TRIGGER TABLENAME_TRIGGER
BEFORE INSERT ON TABLENAME
REFERENCING NEW AS NEW
FOR EACH ROW
DECLARE
LATEST VERSION NUMBER;
BEGIN
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;
END;


This gives me a mutating table error, I assume because I'm trying to peek into TABLENAME to find the max(version) while inserting a new row.

How can I make this approach work, or if I'm totally wrong headed, what's a better approach?

Answer

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.

Something like

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

Comments