sql - 更新列时插入历史记录的 PL SQL 触发器

Question

当表中的任何列更新时，我想在历史表中插入一行。

我只想捕获列名、旧值和新值。

我希望这个触发器尽可能可重用，因为我将在其他表上使用相同的概念。

我熟悉触发器以及如何捕获一列的更新。我正在专门寻找如何编写 one 触发器，该触发器将记录插入到历史表的 any 列中，该列在历史表的相应表中得到更新。

编辑 1
我在我的帖子中声明了 NOWHERE 我正在寻找源代码，这让那些对我投反对票并认为我正在寻找源代码的人感到羞耻。您可以查看我以前的问题/答案，看看我不是在寻找“免费源代码”。

正如我在最初的问题中所述，我正在寻找如何编写此内容。我检查了 http://plsql-tutorial.com/plsql-triggers.htm并且有一个代码块显示了如何为更新一列时编写触发器。我想也许有人会知道如何为我介绍的场景提供更通用的触发器。

Answer 1

假设是普通表而不是对象表，您没有太多选择。您的触发器必须是某种形式

CREATE OR REPLACE TRIGGER trigger_name
  AFTER UPDATE ON table_name
  FOR EACH ROW
BEGIN
  IF( UPDATING( 'COLUMN1' ) )
  THEN
    INSERT INTO log_table( column_name, column_value )
      VALUES( 'COLUMN1', :new.column1 );
  END IF;

  IF( UPDATING( 'COLUMN2' ) )
  THEN
    INSERT INTO log_table( column_name, column_value )
      VALUES( 'COLUMN2', :new.column2 );
  END IF;

  <<repeat for all columns>>
END;

您可以获取 COLUMN1 , COLUMN2 , ... COLUMN<<n>>来自数据字典 ( USER_TAB_COLS ) 的字符串而不是对它们进行硬编码，但您仍然必须对 :new 中的列的引用进行硬编码伪记录。

您可能会编写一段代码，通过查询数据字典(最有可能是 USER_TAB_COLS 或 ALL_TAB_COLS)生成上述触发器，使用 DDL 语句构建字符串，然后执行 EXECUTE IMMEDIATE执行 DDL 语句。然后，每当将新列添加到任何表以重新创建该列的触发器时，您都必须调用此脚本。编写和调试这种 DDL 生成代码很乏味，但在技术上并不是特别具有挑战性。但这很少是值得的，因为有人不可避免地添加了一个新列而忘记了重新运行脚本，或者有人需要修改触发器来做一些额外的工作，而且手动更新触发器比修改和测试生成的脚本更容易触发器。

不过，更一般地说，我会质疑以这种方式存储数据是否明智。为修改的每一行的每一列在历史表中存储一行使得使用历史数据非常具有挑战性。如果有人想知道特定行在特定时间点处于什么状态，则必须将历史表与其自身连接 N 次，其中 N 是该时间点表中的列数。这将是非常低效的，很快就会让人们避免尝试使用历史数据，因为他们无法在合理的时间内用它做有用的事情而不用撕掉他们的头发。通常情况下，使用与实时表具有相同列集的历史表(添加一些用于跟踪日期等)并在每次更新行时在历史表中插入一行会更有效。这将占用更多空间，但通常更易于使用。

Oracle 有多种方法来审计数据更改——您可以 AUDIT DML，可以使用细粒度审计(FGA)，可以使用Workspace Manager，也可以使用Oracle Total Recall。如果您正在寻找比编写自己的触发代码更大的灵 active ，我强烈建议您研究这些本质上更加自动化的其他技术，而不是尝试开发您自己的架构。