Oracle - 触发器在更新时创建历史行

Question

首先，我们目前拥有所需的行为，但当需要对数据库进行任何更改时，维护起来并不容易。我正在寻找更简单、更高效或更易于维护的东西（任何满足这三点的东西都是最受欢迎的）。当我们执行更新时，会创建一个历史行，它是当前行的副本，然后更新当前行的值。结果是我们有该行在更新之前的历史记录。

推理：我们必须遵守许多联邦规则，并通过这种方式获得所有内容的完整审核历史记录，并且我们可以随时查看数据库并了解事情的情况（未来的要求） 。 出于类似的原因，我无法更改历史记录的方式...任何解决方案都必须产生与当前触发器创建的数据相同的数据。

Contact 表的当前触发器如下所示：
（为简洁起见，删除无用字段，字段数量无关紧要）

更新前（每行）：

DECLARE
     indexnb number;
BEGIN
  :new.date_modified := '31-DEC-9999';
  indexnb := STATE_PKG.newCONTACTRows.count + 1;
  :new.date_start := sysdate;
  :new.version := :old.version + 1;
  state_pkg.newCONTACTRows(indexnb).ID := :old.ID;
  state_pkg.newCONTACTRows(indexnb).PREFIX := :old.PREFIX;
  state_pkg.newCONTACTRows(indexnb).FIRST_NAME := :old.FIRST_NAME;
  state_pkg.newCONTACTRows(indexnb).MIDDLE_NAME := :old.MIDDLE_NAME;
  state_pkg.newCONTACTRows(indexnb).LAST_NAME := :old.LAST_NAME;
  --Audit columns after this
  state_pkg.newCONTACTRows(indexnb).OWNER := :old.OWNER;
  state_pkg.newCONTACTRows(indexnb).LAST_USER := :old.LAST_USER;
  state_pkg.newCONTACTRows(indexnb).DATE_CREATED := :old.DATE_CREATED;
  state_pkg.newCONTACTRows(indexnb).DATE_MODIFIED := sysdate;
  state_pkg.newCONTACTRows(indexnb).VERSION := :old.VERSION;
  state_pkg.newCONTACTRows(indexnb).ENTITY_ID := :old.id;
  state_pkg.newCONTACTRows(indexnb).RECORD_STATUS := :old.RECORD_STATUS;
  state_pkg.newCONTACTRows(indexnb).DATE_START := :old.DATE_START;
END;

更新前（所有行一次）：

BEGIN
  state_pkg.newCONTACTRows := state_pkg.eCONTACTRows;
END;

更新后（所有行一次）：

DECLARE
BEGIN
  for i in 1 .. STATE_PKG.newCONTACTRows.COUNT loop
    INSERT INTO "CONTACT" (
      ID, 
      PREFIX, 
      FIRST_NAME, 
      MIDDLE_NAME, 
      LAST_NAME, 
      OWNER, 
      LAST_USER, 
      DATE_CREATED, 
      DATE_MODIFIED, 
      VERSION, 
      ENTITY_ID, 
      RECORD_STATUS, 
      DATE_START)
    VALUES (
      CONTACT_SEQ.NEXTVAL, 
      state_pkg.newCONTACTRows(i).PREFIX,
      state_pkg.newCONTACTRows(i).FIRST_NAME,
      state_pkg.newCONTACTRows(i).MIDDLE_NAME,
      state_pkg.newCONTACTRows(i).LAST_NAME,
      state_pkg.newCONTACTRows(i).OWNER,
      state_pkg.newCONTACTRows(i).LAST_USER,
      state_pkg.newCONTACTRows(i).DATE_CREATED,
      state_pkg.newCONTACTRows(i).DATE_MODIFIED,
      state_pkg.newCONTACTRows(i).VERSION,
      state_pkg.newCONTACTRows(i).ENTITY_ID,
      state_pkg.newCONTACTRows(i).RECORD_STATUS,
      state_pkg.newCONTACTRows(i).DATE_START
    );
  end loop;
END;

包定义为（修剪后的完整版本只是每个表的副本）：

PACKAGE STATE_PKG IS
  TYPE CONTACTArray IS TABLE OF CONTACT%ROWTYPE INDEX BY BINARY_INTEGER; 
  newCONTACTRows CONTACTArray; 
  eCONTACTRows CONTACTArray;
END;

当前结果

这是生成的历史记录示例：

ID    First Last   Ver  Entity_ID  Date_Start              Date_Modified  
1196  John  Smith  5    0          12/11/2009 10:20:11 PM  12/31/9999 12:00:00 AM
1201  John  Smith  0    1196       12/11/2009 09:35:20 PM  12/11/2009 10:16:49 PM
1203  John  Smith  1    1196       12/11/2009 10:16:49 PM  12/11/2009 10:17:07 PM
1205  John  Smith  2    1196       12/11/2009 10:17:07 PM  12/11/2009 10:17:19 PM
1207  John  Smith  3    1196       12/11/2009 10:17:19 PM  12/11/2009 10:20:00 PM
1209  John  Smith  4    1196       12/11/2009 10:20:00 PM  12/11/2009 10:20:11 PM

每个历史记录都有一个 Entity_ID，它是当前行的 ID，新记录上的 Date_Start 与该记录的 Date_Modified 相匹配最后的历史记录行。这允许我们执行像 Where Entity_ID = :id Or ID = :id And :myDate 这样的查询Date_Modified 和 :myDate >= Date_Start。可以通过 Entity_ID = :current_id 获取历史记录。

是否有更好的方法，希望更容易维护/更灵活？这个概念很简单，当更新一行时，通过插入旧值将其复制到同一个表，然后更新当前行row...但实际上这样做，我还没有找到更简单的方法。我希望 Oracle 中更狡猾/更聪明的人有更好的方法来解决这个问题。速度并不重要，像大多数 Web 应用程序一样，我们 99% 读取 1% 写入，所有批量操作都是插入，而不是不会创建任何历史记录的更新。

如果有人有任何想法来简化维护，我将非常感激，谢谢！

Answer 1

好吧，这是重写。当我第一次回复时，我错过的是应用程序将其历史记录存储在主表中。现在我明白为什么@NickCraver 对代码如此抱歉。

首先要做的就是追捕这种设计的肇事者，并确保他们不再这样做。像这样存储历史记录无法扩展，使正常（非历史）查询变得更加复杂，并破坏关系完整性。显然，有些情况下这些都不重要，也许您的网站就是其中之一，但总的来说，这是一个非常糟糕的实现。

最好的方法是 Oracle 11g Total Recall< /a>.这是一个优雅的解决方案，具有完全隐形且高效的实施方式，而且（按照 Oracle 的其他收费附加服务的标准）价格相当合理。

但如果《全面回忆》是不可能的，并且您确实必须这样做，则不允许更新。对现有 CONTACT 记录的更改应该是插入。为了使这项工作有效，您可能需要使用 INSTEAD OF 触发器构建一个视图。它仍然令人讨厌，但不像现在那么令人讨厌。

---

从 Oracle 11.2.0.4 开始，Total Recall 已更名为 Flashback Archive，并作为企业许可证的一部分包含在内（尽管除非我们购买高级压缩选项，否则会删除压缩日志表）。

Oracle 的这种慷慨应该使 FDA 成为存储历史记录的正常方式：它高效、执行力强，它是 Oracle 内置的标准语法，支持历史查询。唉，我预计多年来仍会看到半生不熟的实现，其中包括触发器混乱、主键损坏和糟糕的性能。因为日志记录似乎是开发人员喜欢的干扰之一，尽管事实上它是低级管道，与 99.99% 的业务运营基本上无关。

Answer 2

不幸的是，无法避免在触发器中引用所有列名称（:OLD.this、:OLD.that 等）。但是，您可以编写一个程序来从表定义（在 USER_TAB_COLS 中）生成触发代码。然后，每当表发生更改时，您都可以生成并编译触发器的新副本。

请参阅此 AskTom 主题 了解如何做到这一点。

Answer 3

如果有人有与我们相同的高度专业化的情况（Linq 访问使单表历史更加清晰/更容易，这就是我最终所做的来简化我们所拥有的，欢迎任何改进......这只是一个脚本，它将每当数据库发生变化时运行，重新生成审计触发器，主要变化是 PRAGMA AUTONOMOUS_TRANSACTION; 将生成的历史记录放在自治事务上，而不关心突变（这与我们如何审计无关）：

Declare
  cur_trig varchar(4000);
  has_ver number;
Begin
    For seq in (Select table_name, sequence_name 
              From user_tables ut, user_sequences us
              Where sequence_name = replace(table_name, '_','') || '_SEQ'
                And table_name Not Like '%$%'
                And Exists (Select 1
                            From User_Tab_Columns utc
                            Where Column_Name = 'ID' And ut.table_name = utc.table_name)
                And Exists (Select 1
                            From User_Tab_Columns utc
                            Where Column_Name = 'DATE_START' And ut.table_name = utc.table_name)
                And Exists (Select 1
                            From User_Tab_Columns utc
                            Where Column_Name = 'DATE_MODIFIED' And ut.table_name = utc.table_name))
    Loop
     --ID Insert Triggers (Autonumber for oracle!)
     cur_trig := 'CREATE OR REPLACE TRIGGER ' || seq.table_name || 'CR' || chr(10)
              || 'BEFORE INSERT ON ' || seq.table_name || chr(10)
              || 'FOR EACH ROW' || chr(10)
              || 'BEGIN' || chr(10)
              || '  SELECT ' || seq.sequence_name || '.NEXTVAL INTO :new.ID FROM DUAL;' || chr(10)
              || '  IF(:NEW.ENTITY_ID = 0) THEN' || chr(10)
              || '    SELECT sysdate, sysdate, ''31-DEC-9999'' INTO :NEW.DATE_CREATED, :NEW.DATE_START, :NEW.DATE_MODIFIED FROM DUAL;' || chr(10)
              || '  END IF;' || chr(10)
              || 'END;' || chr(10);

     Execute Immediate cur_trig;

     --History on update Triggers
     cur_trig := 'CREATE OR REPLACE TRIGGER ' || seq.table_name || '_HIST' || chr(10)
              || '  BEFORE UPDATE ON ' || seq.table_name || ' FOR EACH ROW' || chr(10)
              || 'DECLARE' || chr(10)
              || '  PRAGMA AUTONOMOUS_TRANSACTION;' || chr(10)
              || 'BEGIN' || chr(10)
              || '  INSERT INTO ' || seq.table_name || ' (' || chr(10)
              || '   DATE_MODIFIED ' || chr(10)
              || '   ,ENTITY_ID ' || chr(10);

       For col in (Select column_name
                 From user_tab_columns ut
                 Where table_name = seq.table_name
                   And column_name NOT In ('ID','DATE_MODIFIED','ENTITY_ID')
                 Order By column_name)
     Loop
       cur_trig := cur_trig || '   ,' || col.column_name || chr(10);
     End Loop;

     cur_trig := cur_trig || ') VALUES ( --ID is Automatic via another trigger' || chr(10)
                          || '   SYSDATE --DateModified Set' || chr(10)
                          || '   ,:old.ID --EntityID Set' || chr(10);

     has_ver := 0;
       For col in (Select column_name
                 From user_tab_columns ut
                 Where table_name = seq.table_name
                   And column_name NOT In ('ID','DATE_MODIFIED','ENTITY_ID')
                 Order By column_name)
     Loop
       cur_trig := cur_trig || '   ,:old.' || col.column_name || chr(10);
       If Upper(col.column_name) = 'VERSION' Then 
         has_ver := 1; 
       End If;
     End Loop;

     cur_trig := cur_trig || ');' || chr(10)
                          || ':new.DATE_MODIFIED := ''31-DEC-9999'';' || chr(10)
                          || ':new.DATE_START := SYSDATE;' || chr(10);
     If has_ver = 1 Then
       cur_trig := cur_trig || ':new.version := :old.version + 1;' || chr(10);
     End If;
     cur_trig := cur_trig || 'COMMIT;' || chr(10)
                          || 'END;' || chr(10);

     Execute Immediate cur_trig;
    End Loop;
End;
/

如果您可以改进，请随意...我只编写了一些 PL/SQL 脚本，这种需求并不经常出现...可能还有很多需要改进的地方，

答案归功于 APC 让我更努力地看待这个问题。我不推荐这种历史布局，除非它是你的模型/应用程序/的其余部分堆栈非常好 对于此应用程序，我们不断地显示历史和当前的混合，并且当涉及 Linq-to-SQL 样式访问时，过滤比组合简单得多。感谢所有的答案，所有好的建议......当我有更多的时间并且不受发布时间表的困扰时，我会重新审视它，看看是否可以进一步改进。

Answer 4

我了解您的特定应用程序要求将历史记录和当前值放在同一个表中，但这也许可以通过采用更常见的途径来处理，即拥有一个单独的审计表，但将其构建为伪物化视图以呈现一个应用程序的组合视图。

对我来说，这样做的优点是拥有一个简单的“当前”视图和一个单独但完全自动化的“审核”视图（在本例中也具有当前视图）。

像这样的东西：

create sequence seq_contact start with 1000 increment by 1 nocache nocycle;

create table contact (
    contact_id integer,
    first_name varchar2(120 char),
    last_name varchar2(120 char),
    last_update_date date
    );

alter table contact add constraint pk_contact primary key (contact_id);

create table a$contact (
    version_id integer,
    contact_id integer,
    first_name varchar2(120 char),
    last_name varchar2(120 char),
    last_update_date date
    );

alter table a$contact add constraint pk_a$contact primary key
        (contact_id, version_id);

create or replace trigger trg_contact
before insert or delete or update on contact 
for each row
declare

    v_row contact%rowtype;
    v_audit a$contact%rowtype;

begin

    select seq_contact.nextval into v_audit.version_id from dual;

    if not deleting then

        :new.last_update_date := sysdate;

    end if;

    if inserting or updating then

        v_audit.contact_id := :new.contact_id;
        v_audit.first_name := :new.first_name;
        v_audit.last_name := :new.last_name;
        v_audit.last_update_date := :new.last_update_date;

    elsif deleting then

        v_audit.contact_id := :old.contact_id;
        v_audit.first_name := :old.first_name;
        v_audit.last_name := :old.last_name;
        v_audit.last_update_date := sysdate;

    end if;

    insert into a$contact values v_audit;

end trg_contact;
/

insert into contact (contact_id, first_name, last_name) values
    (1,'Nick','Pierpoint');

insert into contact (contact_id, first_name, last_name) values
    (2, 'John', 'Coltrane');

insert into contact (contact_id, first_name, last_name) values
    (3, 'Sonny', 'Rollins');

insert into contact (contact_id, first_name, last_name) values
    (4, 'Kenny', 'Wheeler');

update contact set last_name = 'Cage' where contact_id = 1;

delete from contact where contact_id = 1;

update contact set first_name = 'Zowie' where contact_id in  (2,3);

select * from a$contact order by contact_id, version_id;

VERSION_ID  CONTACT_ID  FIRST_NAME  LAST_NAME  LAST_UPDATE_DATE
1000        1           Nick        Pierpoint  11/02/2010 14:53:49
1004        1           Nick        Cage       11/02/2010 14:54:00
1005        1           Nick        Cage       11/02/2010 14:54:06
1001        2           John        Coltrane   11/02/2010 14:53:50
1006        2           Zowie       Coltrane   11/02/2010 14:54:42
1002        3           Sonny       Rollins    11/02/2010 14:53:51
1007        3           Zowie       Rollins    11/02/2010 14:54:42
1003        4           Kenny       Wheeler    11/02/2010 14:53:53

Answer 5

如果您想开发通用解决方案，您可能需要查看 DBMS_SQL 包。使用它，您可以开发一个包/过程，该包/过程将表名作为输入，并通过检查字典中的表结构并动态构建更新来基于该表名构建更新。这将是不平凡的前期开发，但未来的维护会少得多，因为如果表结构发生变化，代码会感知到并进行调整。此方法适用于您愿意使用它的任何表。

Answer 6

根据数据库的复杂性（表的数量、大小、PK/FK 关系的深度、触发器中的其他逻辑），您可能需要查看 Oracle Workspace Management。您进行 API 调用以将表置于工作区管理之下，这会导致 Oracle 用可更新视图和其他维护行的所有版本历史记录的相应对象替换该表。

我已经使用过这个方法，虽然有缺点，但审计的一个优点是代码对象都是由 Oracle 生成的，并且通常假定它们的正确性。

Answer 7

我唯一可能建议将历史记录存储在与“当前”记录相同的表中是当 FK 链接到记录必须或可能需要链接到它们时。例如，我见过的一个应用程序有一些 FK 链接，这些链接会链接到“时间点”的记录，也就是说，如果记录更新了，FK 仍然会链接到历史记录 - 这是一个设计的重要部分，将历史记录分离到第二个表中会使其更加笨重。

除此之外，我更希望跟踪所有更改的业务需求应该使用每个表的单独“历史记录”表来解决。当然，这意味着更多的 DDL，但它极大地简化了应用程序代码，并且您还将受益于更好的性能和可扩展性。