如何跟踪参考表的历史更改？

Question

这是Web应用程序中非常普遍的事情。如果我有一个用户表，并且想跟踪对用户表所做的所有更改，则可以使用数据库插入和更新触发器将这些更改保存在user_history表中。

但是如果我有user_products表，我有user_id，product_id，cost。当我在系统中添加一个用户时，可以说我有两个与该用户相关的产品。所以我的user_products表将为该用户提供两行。

user_id product_id cost 
1  10   1000 
2  20   2000 

现在，如果我去编辑用户页面和删除产品1，增加产品3，成本的变化对产品2从2000年到3000

所以通常我删除所有记录的user_id 1从user_product表，然后为新产品做一个新的插入。

所以它不是一个定期的更新，而是一个删除然后插入。因此我无法跟踪历史变化。

理想我想知道，我已删除的产品1，高附加值产品3，改变成本的产品2从2000年到3000

编辑1： -

我没有做一个更新。我正在做一个删除，然后插入。所以我删除记录与产品id 2和成本2000.然后再次插入记录与prod id 2但成本3000.所以技术上它的删除和插入，但逻辑上只有成本从2000更改为3000.如果我检查时同时执行查询它会说我删除了产品ID 2，然后添加产品与ID 2是相同的。但是，我希望能够看到成本已经从2000年chnaged到3000

Answer 1

一种选择是创建一个由触发器填充上user_product一个user_product_history表，然后定义转换旧“删除”排在历史表为update，如果该行被随后插入的触发器。

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; 

从效率的角度来看，然而，这样的删除和插入，而不是更新将意味着，你将远远更多的负载您的数据库比是必要的。你会做比实际需要更多的I/O。最终你会得到更复杂的代码来处理更改。你几乎肯定会更好地弄清楚已经发生了什么变化，然后只是更新这些行。

Answer 2

我不知道是否有一个“标准”的方式或简单的一个，但我的经验，你必须做你自己.. 当然，你不需要为每个表编写一个特定的方法，而只需要一个处理所有更新SQL的通用方法，根据你的表结构，这是一个聪明的东西。 例如： 可以定义将有一个历史表： ID - 表名 - 表记录的ID - 字段列表更新 - 值的列表前更新 - 更新完毕后

值的名单，然后，到你的代码，你应该有一个抽象类，将处理SQL查询：上更新，调用将解析SQL查询和记录插入该表的方法，即使你要查询的SELECT来检索更新前的数据（开销最小，因为SELECT只使用少量资源，并且可以使用数据库服务器缓存）。 最后，当显示相对于记录的信息，比方说用户，您可以添加代码来显示该表和该用户ID的历史。

希望它能帮助你。