Oracle - Triggers to create a history row on update

Question

First, we currently have the behavior that's desired, but it's not trivial to maintain when any changes to the database are needed. I'm looking for anything simpler, more efficient, or easier to maintain (anything that does any of those 3 would be most welcome). When we perform an update, a history row is created that is a copy of the current row, and the current row's values are then updated. The result being that we have a history record of how the row was before it was updated.

Reasoning: We have to be compliant with a number of federal rules, and went this route to have a full audit history of everything, as well as we can look at the database at any point in time and see how things looked (future requirement). For similar reasons, I cannot change how history is recorded...any solution must result in the same data as the current triggers create.

Here's what the current triggers look like for the Contact Table:
(stripped useless fields for brevity, the number of fields doesn't matter)

Before update (each row):

DECLARE
     indexnb number;
BEGIN
  :new.date_modified := '31-DEC-9999';
  indexnb := STATE_PKG.newCONTACTRows.count + 1;
  :new.date_start := sysdate;
  :new.version := :old.version + 1;
  state_pkg.newCONTACTRows(indexnb).ID := :old.ID;
  state_pkg.newCONTACTRows(indexnb).PREFIX := :old.PREFIX;
  state_pkg.newCONTACTRows(indexnb).FIRST_NAME := :old.FIRST_NAME;
  state_pkg.newCONTACTRows(indexnb).MIDDLE_NAME := :old.MIDDLE_NAME;
  state_pkg.newCONTACTRows(indexnb).LAST_NAME := :old.LAST_NAME;
  --Audit columns after this
  state_pkg.newCONTACTRows(indexnb).OWNER := :old.OWNER;
  state_pkg.newCONTACTRows(indexnb).LAST_USER := :old.LAST_USER;
  state_pkg.newCONTACTRows(indexnb).DATE_CREATED := :old.DATE_CREATED;
  state_pkg.newCONTACTRows(indexnb).DATE_MODIFIED := sysdate;
  state_pkg.newCONTACTRows(indexnb).VERSION := :old.VERSION;
  state_pkg.newCONTACTRows(indexnb).ENTITY_ID := :old.id;
  state_pkg.newCONTACTRows(indexnb).RECORD_STATUS := :old.RECORD_STATUS;
  state_pkg.newCONTACTRows(indexnb).DATE_START := :old.DATE_START;
END;

Before update (once for all rows):

BEGIN
  state_pkg.newCONTACTRows := state_pkg.eCONTACTRows;
END;

After update (once for all rows):

DECLARE
BEGIN
  for i in 1 .. STATE_PKG.newCONTACTRows.COUNT loop
    INSERT INTO "CONTACT" (
      ID, 
      PREFIX, 
      FIRST_NAME, 
      MIDDLE_NAME, 
      LAST_NAME, 
      OWNER, 
      LAST_USER, 
      DATE_CREATED, 
      DATE_MODIFIED, 
      VERSION, 
      ENTITY_ID, 
      RECORD_STATUS, 
      DATE_START)
    VALUES (
      CONTACT_SEQ.NEXTVAL, 
      state_pkg.newCONTACTRows(i).PREFIX,
      state_pkg.newCONTACTRows(i).FIRST_NAME,
      state_pkg.newCONTACTRows(i).MIDDLE_NAME,
      state_pkg.newCONTACTRows(i).LAST_NAME,
      state_pkg.newCONTACTRows(i).OWNER,
      state_pkg.newCONTACTRows(i).LAST_USER,
      state_pkg.newCONTACTRows(i).DATE_CREATED,
      state_pkg.newCONTACTRows(i).DATE_MODIFIED,
      state_pkg.newCONTACTRows(i).VERSION,
      state_pkg.newCONTACTRows(i).ENTITY_ID,
      state_pkg.newCONTACTRows(i).RECORD_STATUS,
      state_pkg.newCONTACTRows(i).DATE_START
    );
  end loop;
END;

The package defined as (trimmed, full version is just copy of this per table):

PACKAGE STATE_PKG IS
  TYPE CONTACTArray IS TABLE OF CONTACT%ROWTYPE INDEX BY BINARY_INTEGER; 
  newCONTACTRows CONTACTArray; 
  eCONTACTRows CONTACTArray;
END;

The current result

Here's a resulting history sample:

ID    First Last   Ver  Entity_ID  Date_Start              Date_Modified  
1196  John  Smith  5    0          12/11/2009 10:20:11 PM  12/31/9999 12:00:00 AM
1201  John  Smith  0    1196       12/11/2009 09:35:20 PM  12/11/2009 10:16:49 PM
1203  John  Smith  1    1196       12/11/2009 10:16:49 PM  12/11/2009 10:17:07 PM
1205  John  Smith  2    1196       12/11/2009 10:17:07 PM  12/11/2009 10:17:19 PM
1207  John  Smith  3    1196       12/11/2009 10:17:19 PM  12/11/2009 10:20:00 PM
1209  John  Smith  4    1196       12/11/2009 10:20:00 PM  12/11/2009 10:20:11 PM

Each history record has an Entity_ID that's the ID of the current row, the Date_Start on the new record matches the Date_Modified of the last history row. This allows us to do queries like Where Entity_ID = :id Or ID = :id And :myDate < Date_Modified And :myDate >= Date_Start. History can be fetched by Entity_ID = :current_id.

Is there a better approach, hopefully more maintainable/flexible to do this? The concept is simple, when updating a row, copy it to the same table via an insert with the old values, then update the current row...but actually doing that, I have yet to find a simpler way. I'm hoping someone much trickier/wiser in Oracle has a better approach to this. Speed doesn't matter much, we're 99% reads 1% writes like most web applications, and all bulk operations are inserts, not updates which wouldn't create any history.

If anyone has any ideas to simplify the maintenance on this, I'd be extremely appreciative, thanks!

Answer 1

Okay, this is a rewrite. What I missed when I first responded is that the application is storing its History in the main table. Now I understand why @NickCraver is so apologetic about the code.

Well the first thing to do is to hunt down the perpetrators of this design and make sure they never do it again. Storing history like this doesn't scale, makes normal (non-historical) queries more complicated and sabotages relational integrity. Obviously there are scenarios where none of that matters, and perhaps your site is one of them, but in general this is a very bad implementation.

The best way of doing this is Oracle 11g Total Recall. It's an elegant solution, with a completely invisible and effcient implementation, and - by the standards of Oracle's other chargeable extras - quite reasonably priced.

But if Total Recall is out of the question and you really must do it this, don't allow updates. A change to an existing CONTACT record should be an insert. In order to make this work you may need to build a view with an INSTEAD OF trigger. It's still yucky but not quite as yucky as what you have now.

---

As of Oracle 11.2.0.4 Total Recall has been rebranded Flashback Archive and is included as part of the Enterprise License (although shorn of the compressed journal tables unless we purchase the Advanced Compress option).

This largesse from Oracle ought to make FDA the normal way of storing history: it's efficient, it's performative, it's an Oracle built-in with standard syntax to support historical queries. Alas I expect to see half-cooked implementations with spatchcocked triggers, broken primary keys and horrible performance for many years yet. Because journalling seems to be one of those distractions which developers delight in, despite the fact that it's low-level plumbing which is largely irrelevant to 99.99% of all business operations.