Oracle - 在更新时触发以创建历史记录行 [英] Oracle - Triggers to create a history row on update
问题描述
首先，我们目前拥有所需的行为，但是当需要对数据库进行任何更改时，维护起来并非易事.我正在寻找任何更简单、更高效或更易于维护的东西(任何能做到这 3 项中的任何一项的东西都是最受欢迎的).当我们执行更新时，会创建一个历史记录行，它是当前 行的副本，然后更新当前行的值.结果是我们有该行在更新之前的历史记录.
First, we currently have the behavior that's desired, but it's not trivial to maintain when any changes to the database are needed. I'm looking for anything simpler, more efficient, or easier to maintain (anything that does any of those 3 would be most welcome). When we perform an update, a history row is created that is a copy of the current row, and the current row's values are then updated. The result being that we have a history record of how the row was before it was updated.
推理:我们必须遵守一些联邦规则，并通过这条路线对所有内容进行完整的审计历史记录，并且我们可以随时查看数据库并查看情况(未来需求).出于类似的原因，我无法更改历史记录的方式...任何解决方案都必须产生与当前触发器创建的数据相同的数据.
Reasoning: We have to be compliant with a number of federal rules, and went this route to have a full audit history of everything, as well as we can look at the database at any point in time and see how things looked (future requirement). For similar reasons, I cannot change how history is recorded...any solution must result in the same data as the current triggers create.
以下是 Contact 表的当前触发器:
(为了简洁，去掉了无用的字段，字段的数量无关紧要)
Here's what the current triggers look like for the Contact Table:
(stripped useless fields for brevity, the number of fields doesn't matter)
更新前(每行):
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;
更新前(所有行一次):
Before update (once for all rows):
BEGIN
state_pkg.newCONTACTRows := state_pkg.eCONTACTRows;
END;
更新后(所有行一次):
After update (once for all rows):
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;
定义为(修剪后的完整版只是每个表的副本)的包:
The package defined as (trimmed, full version is just copy of this per table):
PACKAGE STATE_PKG IS
TYPE CONTACTArray IS TABLE OF CONTACT%ROWTYPE INDEX BY BINARY_INTEGER;
newCONTACTRows CONTACTArray;
eCONTACTRows CONTACTArray;
END;
当前结果
这是一个结果历史样本:
The current result
Here's a resulting history sample:
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 获取.
Each history record has an Entity_ID that's the ID of the current row, the Date_Start on the new record matches the Date_Modified of the last history row. This allows us to do queries like Where Entity_ID = :id Or ID = :id And :myDate < Date_Modified And :myDate >= Date_Start. History can be fetched by Entity_ID = :current_id.
是否有更好的方法，希望更易于维护/更灵活? 概念很简单，更新行时，通过插入旧值将其复制到同一个表，然后更新当前行...但实际上这样做，我还没有找到更简单的方法.我希望 Oracle 中更狡猾/更聪明的人对此有更好的方法.速度并不重要，我们 99% 读取 1% 写入与大多数 Web 应用程序一样，并且所有批量操作都是插入，而不是不会创建任何历史记录的更新.
Is there a better approach, hopefully more maintainable/flexible to do this? The concept is simple, when updating a row, copy it to the same table via an insert with the old values, then update the current row...but actually doing that, I have yet to find a simpler way. I'm hoping someone much trickier/wiser in Oracle has a better approach to this. Speed doesn't matter much, we're 99% reads 1% writes like most web applications, and all bulk operations are inserts, not updates which wouldn't create any history.
如果有人对此有任何简化维护的想法，我将不胜感激，谢谢！
If anyone has any ideas to simplify the maintenance on this, I'd be extremely appreciative, thanks!
推荐答案
好的，这是重写.我第一次回复时错过的是应用程序将其历史记录存储在主表中.现在我明白为什么@NickCraver 对代码如此抱歉了.
Okay, this is a rewrite. What I missed when I first responded is that the application is storing its History in the main table. Now I understand why @NickCraver is so apologetic about the code.
首先要做的是追捕这种设计的肇事者，并确保他们再也不会这样做.像这样存储历史不会扩展，使正常(非历史)查询更加复杂并破坏关系完整性.显然，有些情况下这些都不重要，也许您的网站就是其中之一，但总的来说，这是一个非常糟糕的实现.
Well the first thing to do is to hunt down the perpetrators of this design and make sure they never do it again. Storing history like this doesn't scale, makes normal (non-historical) queries more complicated and sabotages relational integrity. Obviously there are scenarios where none of that matters, and perhaps your site is one of them, but in general this is a very bad implementation.
最好的方法是Oracle 11g全面召回.这是一个优雅的解决方案，具有完全不可见且高效的实施，而且 - 按照 Oracle 其他收费附加服务的标准 - 价格相当合理.
The best way of doing this is Oracle 11g Total Recall. It's an elegant solution, with a completely invisible and effcient implementation, and - by the standards of Oracle's other chargeable extras - quite reasonably priced.
但是如果 Total Recall 是不可能的并且您确实必须这样做，不允许更新.对现有 CONTACT 记录的更改应该是插入.为了完成这项工作，您可能需要使用 INSTEAD OF 触发器构建一个视图.它仍然令人讨厌，但不像你现在所拥有的那样令人讨厌.
But if Total Recall is out of the question and you really must do it this, don't allow updates. A change to an existing CONTACT record should be an insert. In order to make this work you may need to build a view with an INSTEAD OF trigger. It's still yucky but not quite as yucky as what you have now.
自 Oracle 11.2.0.4 起，Total Recall 已更名为 Flashback Archive，并作为企业许可的一部分包含在内(尽管除非我们购买 Advanced Compress 选项，否则压缩日志表将被删除).
As of Oracle 11.2.0.4 Total Recall has been rebranded Flashback Archive and is included as part of the Enterprise License (although shorn of the compressed journal tables unless we purchase the Advanced Compress option).
来自 Oracle 的这项慷慨应该使 FDA 成为存储历史的正常方式:它高效、执行力强，它是 Oracle 内置的标准语法来支持历史查询.唉，我希望看到半生不熟的实现，这些实现带有散乱的触发器、损坏的主键和多年来的糟糕性能.因为日记似乎是开发人员喜欢的那些干扰之一，尽管它是低级别的管道，与所有业务运营的 99.99% 基本上无关.
This largesse from Oracle ought to make FDA the normal way of storing history: it's efficient, it's performative, it's an Oracle built-in with standard syntax to support historical queries. Alas I expect to see half-cooked implementations with spatchcocked triggers, broken primary keys and horrible performance for many years yet. Because journalling seems to be one of those distractions which developers delight in, despite the fact that it's low-level plumbing which is largely irrelevant to 99.99% of all business operations.
这篇关于Oracle - 在更新时触发以创建历史记录行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
