creating history from 3 tables

[FALCONSEYE]

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?

http://www.fuzzysiberians.com

 

[LKBrwnDBA]

Sorry, but this sounds kinda stupid:

I'd like to insert one record into 3 history tables at once even if nothing changed in the projects and clients tables.

Why would you want to duplicate the table data if nothing changed?




----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[FALCONSEYE]

My challenge was to access the history. So, I thought if i write to all three history tables even one thing changed, it will be easier. I will simply join 3 history tables.

Otherwise, I have to do something like:

if it's not in table x_history, then go get it from table x, which i have no clue how to.

http://www.fuzzysiberians.com

 

[Thargy]

falconseye,

don't bother. Lookup oracle workspace management. It can implement auditing automatically, and you don't have to write any code. Ask Tom will also have some excellent articles on the subject.

Just use Oracle's built-in facility.

Regards

T

 

[FALCONSEYE]

Wow, work space manager with VIEW WO OVERWRITE is sweet. However, I won't be able to use this feature because of the mgmt.

http://www.fuzzysiberians.com

 

[Thargy]

Ah, so all your managers are qualified DBA's - right?

Regards

T

 

[Dagon]

I've read your description a couple of times, but still have no idea what your requirements are. Why have you got three history tables? What's the difference between them? Why do you need to write to all of them?

For Oracle-related work, contact me through Linked-In.

 

[LKBrwnDBA]

My challenge was to access the history. So, I thought if i write to all three history tables even one thing changed, it will be easier. I will simply join 3 history tables.

Otherwise, I have to do something like:

if it's not in table x_history, then go get it from table x, which i have no clue how to.

I believe your logic is also flawed:

a) You cannot simply join 3 history tables unless you actually duplicate the current data (unchanged row) instances to the history tables (like you are trying to do).

b) You do not query history first and if not found, then try and find the current data!

c) If you do duplicate current unchanged data, what will you do when it actually changes? replace the latest history one? how will you know? Will you keep an indicator (or flag) for each row?

Without duplicating the non-changed data rows to the history tables, the correct query sequence should be:

1) Select the rows in the current table(s) and
2) Query (Join to) the history table(s).



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[FALCONSEYE]

@Thargy (TechnicalUser)
10 Aug 10 9:58
Ah, so all your managers are qualified DBA's - right?

Nothing I can do here.

@Dagon (MIS)
10 Aug 10 11:06
I've read your description a couple of times, but still have no idea what your requirements are. Why have you got three history tables? What's the difference between them? Why do you need to write to all of them?

The idea is to let a project owner (person A) retrieve the latest approved project by the project sponsor (person X). So, say they fill out a form, submit for approval. X approves it. Then A says, hey i need to change the pricing on item z. Then, X denies the request. Now, I have to give A the ability to navigate back to the latest approved project. Three history tables are the copies of the main tables with a dateStamp and a version number.

@LKBrwnDBA (MIS)
a)-b)That is what I found out.
c) Every change in one of the main tables triggers a history insert now. I never replace a history, just keep adding to it. The flag for each row is the version number.

So, I ended up modifying my triggers.

CREATE OR REPLACE TRIGGER INVENTORYMANAGEMENT.trg_projectHistory
before  UPDATE OR DELETE
ON mySchema.projects REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
declare tmpVersion number;
BEGIN
 
   tmpVersion:= myPackage.GETPROJECTVERSION( :OLD.project_ID );     
   
   INSERT INTO mySchema.projectHistory 
        (  thID, project_ID, version ) 
  VALUES
        ( SEQ_PROJECT_HISTORY.NEXTVAL,   
         :OLD.project_ID,
        ...
         tmpVersion
         );


   INSERT INTO mySchema.projectTaskHistory
       ( thID, projectTask_ID,  PROJECT_ID,  LABOR, VERSION ) 
         
    ( select SEQ_PROJECTTASK_HISTORY.NEXTVAL,    
                  projectTask_ID, PROJECT_ID,  LABOR,  tmpVersion 
      from   projectTasks t     
      where t.project_id = :OLD.project_ID );
      
      
   INSERT INTO mySchema.projectTaskClientHistory
         ( vhID, ..., Amount,  version  ) 
         
    ( select SEQ_TASKCLIENT_HIST.NEXTVAL,   ...
                    Amount, Position, tmpVersion
      from    projectTaskClients c2, projectTasks t2     
      where  c2.projectTask_ID =  t2.projectTask_ID 
      and      t2.project_id = :OLD.project_ID );      
  
   EXCEPTION
     WHEN OTHERS THEN
            -- Consider logging the error and then re-raise
       RAISE;
END ;
/

The other triggers are the same. Each one of them has 3 inserts. This might not be the most efficient way of doing this so I am open to any suggestions.

thanks

http://www.fuzzysiberians.com

 

[Dagon]

Some of your phrasing is a bit confusing. When you say "There are 3 main tables: projects, tasks, and clients then 3 history tables for each", I assume you mean there is one history table for each i.e. 3 in total, not 9.

What sort of queries do you want to do against these history tables? Presumably it's not just to find the latest position, as that would just be duplicating what is currently in application tables? Do you want do some sort of point-in-time query or just find what has changed most recently?


For Oracle-related work, contact me through Linked-In.

 

[FALCONSEYE]

Dagon,
3 tables are: projects, tasks, and clients
then
project_history, task_history, client_history. Yes, history tables duplicate what's in the application tables. The application tables show the current status of a project. History tables will let us go back point time to see the previous "versions" of the application tables.

http://www.fuzzysiberians.com

 

[Dagon]

One obvious way to do it without writing to all three tables every time would be just to use from and to dates. Put a from_date and to_date on all the history tables. When you write out a history record, set from_date to be the current datetime at which the event occurred. Locate the previous record for that project with the highest from date and set its to_date to be the current datetime minus 1 second.

Then you can get data from your tables using a query like:

select * from projhist p, taskhist t, clienthist c
where <query date> between 
   p.from_date and nvl(p.to_date, to_date('01-jan-4000', 'DD_MON-YYYY'))
and  <query date> between  t.from_date and nvl(t.to_date, to_date('01-jan-4000', 'DD_MON-YYYY'))
and  <query date> between  c.from_date and nvl(c.to_date, to_date('01-jan-4000', 'DD_MON-YYYY'))
and p.projid = c.projid
and p.projdi = t.projid

The to and from dates should ensure you have a unique set of records for the point in time at which the event occurred.

Because of the update on the to_date, you might have to use statement level triggers rather than row level triggers to implement this, especially if there are constraints between the history tables and the application tables.




For Oracle-related work, contact me through Linked-In.