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

每个历史记录都有一个实体ID，这是当前行的ID，新记录的Date_Start与上一个历史记录的Date_Modified相匹配。这使我们能够执行查询，例如Where Entity_ID = :id Or ID = :id And :myDate < Date_Modified And :myDate >= Date_Start。历史记录可以通过Entity_ID = :current_id获取。

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

如果有任何简化此操作的想法，我将非常感激，谢谢！

- Nick Craver

---

@Nick - 对不起，第一次没有理解到重点。我已经重新修改了我的回答以更确切地解决你的问题。 - APC

Answer 1

如果有人遇到和我们类似的高度专业化的情况（使用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样式访问时，过滤比组合要简单得多。谢谢大家的回答，都是好建议...当我有更多时间而不必受发布计划的限制时，我会重新审视这个问题，看看是否可以进一步改进。

- Nick Craver

Answer 2

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

有关如何执行此操作，请参见this AskTom thread。

- Tony Andrews

---

@Tony - 这是我们目前所做的，运行它并同步多个数据库副本真是一场噩梦...希望有人知道替代方案，例如 select * into temp，仅更新少量审计字段，进行插入...不过我不确定如何解决这个问题，因为 Oracle 不是我的主要领域 :( - Nick Craver

Answer 3

好的，这是一篇重写。当我第一次回答时，我错过了一个细节，就是该应用程序将其历史记录存储在主表中。现在我明白为什么@NickCraver对代码如此道歉。

首先要做的是追查这种设计的罪魁祸首，并确保他们不再这样做。像这样存储历史记录不可伸缩，使正常（非历史）查询更加复杂，破坏关系完整性。显然，在某些情况下，这些都不重要，也许你的网站就是其中之一，但总的来说，这是一种非常糟糕的实现。

最佳方法是使用 Oracle 11g Total Recall。这是一种优雅的解决方案，具有完全隐形和高效的实现，并且 - 根据Oracle的其他收费额外功能的标准 - 价格相当合理。

但如果Total Recall不可行，而你确实必须这样做，不要允许更新。对现有联系记录的更改应为插入。为使此工作正常运行，您可能需要构建一个带有INSTEAD OF触发器的视图。它仍然很糟糕，但并不像现在那样恶心。

---

自Oracle 11.2.0.4起，Total Recall已被重新命名为Flashback Archive，并作为企业许可证的一部分包含在内（尽管缺少压缩日志表，除非我们购买高级压缩选项）。

这个慷慨的赠品应该使FDA成为存储历史记录的常规方式：它是高效的，具有执行性能，是一个带有标准语法支持历史查询的Oracle内置功能。不幸的是，我预计在未来很多年里会看到半煮的实现，不完整的触发器，损坏的主键和可怕的性能。因为记录似乎是开发人员喜欢的那些分心事物之一，尽管它是低级别的管道，对于99.99％的所有业务操作而言基本上没有任何相关性。

- APC

---

дҪ жҳҜиҜҙеҸӘйңҖеңЁдёҖдёӘи§ҰеҸ‘еҷЁдёӯз»„еҗҲдҪҝз”Ё:new.date_modified := '31-DEC-9999'; :new.date_start := sysdate; :new.version := :old.version + 1;е°ұеҸҜд»ҘдәҶеҗ—пјҹиҝҷеҸҜиғҪжңүж•Ҳ...д»ҘеүҚжҲ‘们дёҚиғҪдҪҝз”ЁеҚ•дёӘи§ҰеҸ‘еҷЁзҡ„еҺҹеӣ жҳҜжңүдёҖдёӘжғ…еҶөпјҢдҪҶжҳҜйӮЈжҳҜеҫҲд№…д»ҘеүҚзҡ„дәӢжғ…дәҶпјҢжҲ‘е·Із»Ҹи®°дёҚжё…еҺҹеӣ жҳҜд»Җд№ҲдәҶпјҢи®©жҲ‘иҜ•иҜ•дҪ зҡ„ж–№жі•пјҢзңӢзңӢдјҡеҸ‘з”ҹд»Җд№ҲгҖӮи°ўи°ўпјҒ - Nick Craver

我曾经考虑过这个问题，但是多行插入时会不会出现“表正在变异”的危险？或者现在这样做没问题吗？我记不清了。 - Tony Andrews

@APC - 我试过了...Tony 是正确的，这会产生一个ORA-04091: 表 CONTACT 正在变异，触发器/函数可能无法看到它，这就是为什么我们以前不能走这条路的原因...需要看到它才能记起来，就像大多数古老的事情一样。 - Nick Craver

11g不是不可能的选择，但我们目前使用的是10，不能在没有充分通知的情况下升级。对于扩展性，它涉及到所有层面的最小复杂性。我们经常在Linq-to-Oracle中引用历史记录，并且DB.Contacts.Current()与DB.Contacts之间的区别使得在顶部以一种通用的方式访问历史记录变得容易...这就是背后的原因。基于Entity_Id（=0，！=0）的分区在我们的情况下保持了良好的性能...但是，系统还不是很大。通过改变数据库历史和linq层，可能会找到更好的解决方案，但目前没有时间。 - Nick Craver

@APc - 你的原始解决方案经过一些修改后已经可行，我们完全可以使用匿名事务来实现这个目的。我之前没有遇到过这种解决方法，感谢你让我更加深入地了解了这种简单的方法！ - Nick Craver

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

- Nick Pierpoint

Answer 5

如果您想要开发一个通用的解决方案，您可能需要查看DBMS_SQL包。使用它，您可以开发一个接受表名作为输入并根据该表的结构在字典中检查并实时构建更新的包/过程。这将是前期非常复杂的开发，但以后维护的工作量会大大减少，因为如果表的结构发生变化，代码会感应到并适应。这种方法适用于您希望使用它的任何表格。

- DCookie

Answer 6

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

我使用过这个，虽然有缺点，但审计的一个优点是代码对象都由Oracle生成，其正确性通常是可以假定的。

- dpbradley

---

我看了一下工作区管理工具。它真的很不错，但是有人告诉我我们需要单独购买许可证，而他们不想付费。 - CFNinja

我不确定Oracle的低端产品，但WM是企业版的一部分。 - dpbradley

Answer 7

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

除此之外，我更喜欢为跟踪所有更改的业务需求在每个表格中使用单独的“历史”表来解决问题。当然，这意味着更多的DDL，但它极大地简化了应用程序代码，并且您还将从更好的性能和可伸缩性中受益。

- Jeffrey Kemp

---

实际上，在我们的情况下，单独的历史记录表会让应用程序代码变得更加复杂...我们通过Linq进行访问，访问历史记录或当前记录非常快速/易于处理（在一些通用操作符/扩展方法中处理）。几乎没有代码过滤...但从另一个源获取历史记录的反向过程在我们具体情况下将是巨大的。我当然同意你的一般看法，我们恰好有一个非常专业化的用途/情况，它更加适合...但当然，如果总体解决方案更简单、更容易或更高效，我总是愿意尝试其他选择。 - Nick Craver