CODEWITHSUNDEEP
sqloracle-databasehistory
ajm
ajm

Reputation: 13223

How to keep track of historical changes to reference table?

This is a very common thing in web applications. If I have a user table and I want to keep track of all the changes made to the user table, I can use a database insert and update triggers to save those changes in the user_history table.

But what if I have user_products table, where I have user_id , product_id, cost. When I add a user in the system lets say I have two products associated with that user. So my user_products table will have two rows for that user. 

user_id product_id cost
1       10         1000
2       20         2000

Now if I go to the edit user page and delete product 1 , add product 3 , change cost for product 2 from 2000 to 3000.

so normally I delete all records for user_id 1 from user_product table and then do a new insert for the new products.

So its not a regular update but a delete and then insert. Hence I am not able to keep track of history changes.

Ideally I would want to know that I deleted product 1 , added product 3 , changed cost for product 2 from 2000 to 3000.

EDIT 1:-

I am not doing a update. I am doing a delete and then insert. So I am deleting record with product id 2 and cost 2000. And then again inserting record with prod id 2 but with cost 3000. So technically its delete and insert but logically only cost is changed from 2000 to 3000. If i check while executing both queries it will say i deleted product with id 2 and and then added product with id 2 which are same. But I want to be able to see that the cost has chnaged from 2000 to 3000

Upvotes: 4

Views: 3518

Answers (2)

Justin Cave
Justin Cave

Reputation: 231851

One option would be to create a user_product_history table that is populated by triggers on user_product and then define a trigger that transforms the old 'delete' row in the history table into an update if the row is subsequently inserted.

CREATE TABLE user_product_history (
  user_id         number,
  product_id      number,
  cost            number,
  operation_type  varchar2(1),
  operation_date  date
);

CREATE TRIGGER trg_user_product_history
  AFTER INSERT OR UPDATE OR DELETE ON user_product
  FOR EACH ROW
DECLARE 
  l_cnt integer;
BEGIN
  IF( deleting )
  THEN
    insert into user_product_history( user_id, product_id, cost, operation_type, operation_date )
      values( :old.user_id, :old.product_id, :old.cost, 'D', sysdate );
  ELSIF( updating )
  THEN
    insert into user_product_history( user_id, product_id, cost, operation_type, operation_date )
      values( :new.user_id, :new.product_id, :new.cost, 'U', sysdate );
  ELSIF( inserting )
  THEN
    select count(*)
      into l_cnt
      from user_product_history
     where operation_type = 'D' 
       and user_id        = :new.user_id
       and product_id     = :new.product_id;
    if( l_cnt > 0 )
    then
      update user_product_history
         set operation_type = 'U',
             operation_date = sysdate,
             cost           = :new.cost
       where operation_type = 'D' 
         and user_id        = :new.user_id
         and product_id     = :new.product_id;
    else
      insert into user_product_history( user_id, product_id, cost, operation_type, operation_date )
        values( :new.user_id, :new.product_id, :new.cost, 'I', sysdate );
    end if;
  END IF;
END;

From an efficiency standpoint, however, doing deletes and inserts rather than updates is going to mean that you're putting far more load on your database than is necessary. You'll do substantially more I/O than necessary. And you'll end up with much more complicated code for handling changes. You'll almost certainly be better served figuring out what has changed and then just updating those rows.

Upvotes: 4

Yoong Kim
Yoong Kim

Reputation: 310

I don't know if there is a "standard" way or simple one but for my experience, you have to do it yourself... Of course, you don't need to code a specific method for each table but a generic method that handles all update SQL, something smart according your tables structure. Example: You can define a history table that will have: id - table name - table record id - list of fields to update - list of values updated BEFORE - list of values updated AFTER

Then, into your code, you should have an abstract class that will handle the SQL queries: on update, you call the method that will parse the SQL query and insert a record into this table, even you have to query a SELECT to retrieve the data BEFORE the updates (the overhead is minimum because the SELECT uses only few resources and you can use the DB server cache). Finally, when you display the information relative to a record, let's say the user, you can add the code to display the history on this table AND for this user id.

Hope it will help you.

Upvotes: 1

Related Questions