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

Question

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

4

这是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_product表中删除所有user_id 1的记录，然后插入新产品。

因此，这不是普通的更新，而是删除和再插入。因此，我无法跟踪历史更改。

理想情况下，我希望知道我删除了产品1，添加了产品3，将产品2的成本从2000更改为3000。

编辑1：我没有做更新。我正在进行删除，然后插入。因此，我正在删除具有产品ID 2和成本2000的记录。然后再次插入具有相同产品ID 2但成本为3000的记录。因此，在逻辑上只有成本从2000更改为3000。如果我在执行两个查询时检查它们，它将说我删除了ID为2的产品，然后添加了ID为2的产品，这些产品是相同的。但我希望能够看到成本已从2000变为3000。

- ashishjmeshram

1

为什么不直接进行更新操作，而要进行删除和插入操作呢？ - Dan A.

@Dan A. 这基本上是用户到产品的一对多关系。用户可以拥有数百个产品。如果用户现在有10个产品，以后可能会有40个产品。如果我向用户添加一个新产品，我将不得不检查该用户产品映射是否存在于表中。如果是，则更新；如果不是，则插入。所以对我来说，最简单的方法就是删除所有内容，然后重新插入所有产品。这样我就不必检查现有记录和新记录，并相应地运行插入或更新查询。 - ajm

3

如果你不检查要更新的产品是否存在，从中获得的任何便利都将在尝试跟踪历史时丧失。我认为最好的做法是通过实际进行“技术更新”来执行“逻辑更新”。如果你决定向产品记录添加“创建日期”和“最后更新日期”列，如果更新现有记录，则很容易完成。 - Dan A.

2个回答

1

我不知道是否有一种“标准”或简单的方法，但根据我的经验，你必须自己做... 当然，你不需要为每个表编写特定的方法，而是编写一个通用的方法来处理所有更新SQL，根据你的表结构进行智能处理。例如：你可以定义一个历史记录表，其中包括： id - 表名 - 表记录id - 要更新的字段列表 - 更新前的值列表 - 更新后的值列表

然后，在你的代码中，你应该有一个抽象类来处理SQL查询：在更新时，调用解析SQL查询并将记录插入到此表中的方法，即使你必须查询SELECT以检索更新之前的数据（开销很小，因为SELECT仅使用少量资源，你可以使用DB服务器缓存）。最后，当你显示与记录相关的信息时，比如用户，你可以添加代码来显示此表上的历史记录以及此用户ID。

希望这能帮助到你。

- Yoong Kim

谢谢您的回答。但是我不是在进行更新，而是在进行删除和插入操作。因此，我将删除产品ID为2且成本为2000的记录，然后再次插入具有相同产品ID 2但成本为3000的记录。因此，从技术上讲，这是删除和插入操作，但从逻辑上讲，只有成本从2000更改为3000。如果我在执行这两个查询时进行检查，它会说我删除了ID为2的产品，然后添加了ID为2的产品，这些产品是相同的。但我希望能够看到成本已从2000更改为3000。 - ajm

但是我提到的解决方案是一样的：当我写“更新”时，我的意思是“插入/更新/删除”。为了为您完成解决方案，我建议您插入另一列以表示操作类型：更新/插入/删除。然后在方法中，您可以轻松填充此列，因为SQL查询以选择、插入、更新或删除开头...这就是我使用应用程序时的情况！它运行良好。当然，我的历史记录还有其他字段，例如用户ID、日期、要执行的SQL... - Yoong Kim

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Justin Cave · Accepted Answer

一种选择是创建一个名为user_product_history的表，该表由对user_product的触发器填充，然后定义一个触发器，如果随后插入行，则将历史表中旧的“删除”行转换为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。并且你的代码将变得更加复杂以处理这些变化。因此，最好找出哪些行发生了变化，然后只更新这些行。