oracle and creating history

Question

I am working on a system to track a project's history. There are 3 main tables: projects, tasks, and clients then 3 history tables for each. I have the following trigger on projects table.

 CREATE OR REPLACE TRIGGER mySchema.trg_projectHistory
 BEFORE UPDATE OR DELETE
 ON mySchema.projects REFERENCING NEW AS New OLD AS Old
 FOR EACH ROW
 declare tmpVersion number;
 BEGIN
  select myPackage.GETPROJECTVERSION( :OLD.project_ID ) into tmpVersion from dual;

  INSERT INTO mySchema.projectHistiry
    ( project_ID, ..., version )
  VALUES
    ( :OLD.project_ID,
    ...
     tmpVersion
     );

EXCEPTION
 WHEN OTHERS THEN
   -- Consider logging the error and then re-raise
   RAISE;
END ;
/

I got three triggers for each of my tables (projects, tasks, clients).

Here is the challenge: Not everything changes at the same time. For example, somebody could just update a certain tasks' cost. In this case, only one trigger fires and I got one insert. I'd like to insert one record into 3 history tables at once even if nothing changed in the projects and clients tables.

Also, what if somebody changes a project's end_date, the cost, and say the picks another client. Now, I have three triggers firing at the same time. Only in this case, I will have one record inserted into my three history tables. (which I want)

If i modify the triggers to do insert into 3 tables for the first example, then I will have 9 inserts when the second example happens.

Not quite sure how to tackle this. any help?

Answer 1

From your description, it looks like you would be capturing the effective and end date for each of the history rows once any of the original rows change.

Eg. Project_hist table would have eff_date and exp_date which has the start and end date for a given project. Project table would just have an effective date. (as it is the active project).

I don't see why you want to insert rows for all three history tables when only one of the table values is updated. You can pretty much get the details as you need (as of a given date) using your current logic. (inserting old row in the history table for the table that has been updated only.).

Answer 2

To me it sounds as if you want a transaction-level snapshot of the three tables created whenever you make a change to any of those tables.

Have a row level trigger on each of the three tables that calls a single packaged procedure with the project id and optionally client / task id.

The packaged procedure inserts into all three history tables the relevant project, client and tasks where there isn't already a history record for that key and transaction (ie you don't want duplicates). You got a couple of choices when it comes to the latter. You can use a unique constraint and either a BULK select and insert with FORALL/SAVE EXCEPTIONS, DML error logging (EXCEPTIONS INTO) or a INSERT...SELECT...WHERE NOT EXISTS...

You do need to keep track of your transactions. I'm guessing this is what you were doing with myPackage.GETPROJECTVERSION. The trick here is to only increment versions when you have a new transaction. If, when you get a new version number, you hold it in a pacakge level variable, you can easily tell whether your session has already got a version number or not.

If your session is going to run multiple transaction, you'll need to 'clear' out the session-level version number if it was part of a previous transaction. If you get DBMS_TRANSACTION.LOCAL_TRANSACTION_ID and store that at the package/session level as well, you can determine if you are in a new transaction, or part of the same transaction.

Answer 3

Alternative answer. Have a look at Total Recall / Flashback Archive You can set the retention to 10 years, and use a simple AS OF TIMESTAMP to get the data as of any particular timestamp.

Not sure on performance though. It may be easier to have a daily or weekly retention and then a separate scheduled job that picks out the older versions using the VERSIONS BETWEEN syntax and stores them in your history table.