CODEWITHSUNDEEP
sqloracle-database
monknomo
monknomo

Reputation: 550

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?

Upvotes: 2

Views: 5951

Answers (4)

monknomo
monknomo

Reputation: 550

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

Upvotes: 1

Konstantin Sorokin
Konstantin Sorokin

Reputation: 405

I'll try to answer to the question "How can I make this approach work?". The main troublemaker is the condition "whenever I insert a record, the new record should have a version that is one bigger than the previous max version". So we can't use sequences to effective deal with concurrency. Instead we had to request some kind of explicit lock (exclusive lock on first version of the object for example). Owner of the lock able to select max version of that object without risk to get not actual data. So you may use select for update + insert instead of insert + trigger for example. Or use package API for creating new versions of the object.

Upd: example for simple table tab(id, val, ver)

select * from tab where id = :id and ver = 0 for update;

insert into tab(id, val, ver) values(:id, :new_val, (select max(ver) + 1 from tab where id = :id));

Upvotes: 1

phonetic_man
phonetic_man

Reputation: 1088

Instead of having version number in your table can you try implementing effective from date and effective to date. While inserting a new version for a record update the effective to date of the old record to SYSDATE and for the new version update the effective from date as SYSDATE and effective to date as NULL. The record with a NULL effective to date is your latest record. In this approach you can also generate a version number (logical) using SQL queries (in case you want to show this version number somewhere on a report).

Due to some reason if you want to maintain version number in your table a sequence would be a better choice. Create a sequence for each such table and while inserting a new record use sequence.NEXTVAL in your insert statement itself. This way you will altogether avoid the overhead of the trigger. Though, in this case you will not be guaranteed of a gap free version (in case someone rolls back the transaction after the insert the sequence will produce gaps).

Upvotes: 1

sstan
sstan

Reputation: 36473

I would consider looking into Oracle's Flashback Data Archive functionality instead, available since Oracle version 11.

It would allow you to model your table very naturally (no need for a version column or a trigger), while easily adding very configurable change history tracking. You can then use flashback queries to view past data. It's quite powerful.

Upvotes: 1

Related Questions