原因:我们必须遵守许多联邦规定,因此选择这种方式来获得完整的审计历史记录,以及我们可以随时查看数据库并查看事物的状态(未来要求)。由于类似的原因,我不能更改历史记录的记录方式...任何解决方案都必须产生与当前触发器创建的相同数据。
以下是
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%写入,所有批量操作都是插入操作,而不是更新操作,这不会创建任何历史记录。
如果有任何简化此操作的想法,我将非常感激,谢谢!