Oracle - 在更新时触发以创建历史记录行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2238683/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle - Triggers to create a history row on update
提问by Nick Craver
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 currentrow, 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.
首先，我们目前拥有所需的行为，但在需要对数据库进行任何更改时维护起来并非易事。我正在寻找任何更简单、更高效或更易于维护的东西（任何能做到这 3 项中的任何一项的东西都是最受欢迎的）。当我们执行更新时，会创建一个作为当前行副本的历史记录行，然后更新当前行的值。结果是我们有该行在更新之前的历史记录。
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.
推理：我们必须遵守一些联邦规则，并走这条路线以获得所有内容的完整审计历史，以及我们可以随时查看数据库并查看情况（未来要求） . 出于类似的原因，我无法更改历史记录的方式……任何解决方案都必须产生与当前触发器创建的数据相同的数据。
Here's what the current triggers look like for the ContactTable:
(stripped useless fields for brevity, the number of fields doesn't matter)
以下是Contact表格的当前触发器的样子：（
为了简洁起见，去掉了无用的字段，字段的数量无关紧要）
Before update (each row):
更新前（每行）：
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
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.
每个历史记录都有一个 Entity_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。
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.
有没有更好的方法，希望更易于维护/灵活来做到这一点？这个概念很简单，当更新一行时，通过插入旧值将其复制到同一个表，然后更新当前行......但实际上这样做，我还没有找到更简单的方法。我希望 Oracle 中更狡猾/更聪明的人对此有更好的方法。速度并不重要，我们像大多数 Web 应用程序一样 99% 读取 1% 写入，并且所有批量操作都是插入，而不是不会创建任何历史记录的更新。
If anyone has any ideas to simplify the maintenance on this, I'd be extremely appreciative, thanks!
如果有人有任何想法可以简化对此的维护，我将非常感激，谢谢！
采纳答案by APC
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.
好吧，这是重写。我第一次回复时错过的是应用程序将其历史记录存储在主表中。现在我明白为什么@NickCraver 对代码如此抱歉了。
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.
那么首先要做的是追捕这种设计的肇事者，并确保他们再也不会这样做了。像这样存储历史不会扩展，使正常（非历史）查询更加复杂并破坏关系完整性。显然，有些情况下这些都不重要，也许您的站点就是其中之一，但总的来说，这是一个非常糟糕的实现。
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.
最好的方法是Oracle 11g Total Recall。这是一个优雅的解决方案，具有完全不可见且高效的实施，并且 - 按照 Oracle 其他收费附加服务的标准 - 价格相当合理。
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.
但是，如果 Total Recall 是不可能的并且您确实必须这样做，则不允许更新。对现有 CONTACT 记录的更改应该是插入。为了完成这项工作，您可能需要使用 INSTEAD OF 触发器构建一个视图。它仍然令人讨厌，但不像你现在所拥有的那样令人讨厌。
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 11.2.0.4 起，Total Recall 已更名为 Flashback Archive，并作为企业许可的一部分包含在内（尽管除非我们购买了 Advanced Compress 选项，否则压缩日志表将被删除）。
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 的这种慷慨应该使 FDA 成为存储历史的正常方式：它高效、高效，它是 Oracle 内置的标准语法来支持历史查询。唉，我希望看到半生不熟的实现，这些实现有很多年了。因为日记似乎是开发人员喜欢的那些干扰之一，尽管它是低级别的管道，与所有业务运营的 99.99% 基本上无关。
回答by Tony Andrews
Unfortunately there is no way to avoid referencing all the column names (:OLD.this, :OLD.that, etc.) in triggers. However, what you could do is write a program to generatethe trigger code from the table definition (in USER_TAB_COLS). Then whenever the table is changed you can generate and compile a fresh copy of the triggers.
不幸的是，无法避免在触发器中引用所有列名（:OLD.this、:OLD.that 等）。但是，您可以做的是编写一个程序来从表定义（在 USER_TAB_COLS 中）生成触发器代码。然后，无论何时更改表，您都可以生成并编译触发器的新副本。
See this AskTom threadfor how to do that.
请参阅此 AskTom 线程以了解如何执行此操作。
回答by Nick Craver
In case someone has the same highly specialized case we do (Linq access making single table history much cleaner/easier, this is what I ended up doing to simplify what we have, welcome any improvements....this is just a script that will run whenever the database changes, regenerating the audit triggers, the main change being PRAGMA AUTONOMOUS_TRANSACTION;placing the history generating on an autonomous transaction and not caring about mutation (which doesn't matter for how we audit):
如果有人有我们所做的相同高度专业化的案例（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;
/
If you can improve, feel free...I've only written a handful of PL/SQL scripts, the need doesn't arise often...probably a lot left to be desired there.
如果您可以改进，请随意……我只编写了少数 PL/SQL 脚本，这种需求并不经常出现……可能还有很多需要改进的地方。
Answer credit to APCfor getting me to look at this a bit harder. I don't recommend this history layout unless it its the rest of your model/application/stack extremely well. For this application, we constantly show a mix of history and current, and filtering is far simpler than combining when it comes to a Linq-to-SQL style access. Thanks for all the answers guys, all good suggestions...and when I have more time and am not crunched by a release schedule, this is something I'll revisit to see if it can be improved further.
答信贷APC为让我看一下这个有点难度。我不推荐这种历史布局，除非它是你的模型/应用程序/堆栈的其余部分非常好。对于这个应用程序，我们不断地展示历史和当前的混合，当涉及到 Linq-to-SQL 风格的访问时，过滤比组合简单得多。感谢大家的回答，所有好的建议......当我有更多的时间并且不受发布时间表的影响时，我会重新审视它，看看它是否可以进一步改进。
回答by Nick Pierpoint
I understand your specifc application requirements to have the history and current values in the same table, but perhaps this could be handled by going down the more usual route of having a separate audit table but building it up as a pseudo-materialized view to present a combined view for the application.
我了解您的特定应用程序要求在同一个表中包含历史记录和当前值，但也许这可以通过采用更常见的方式来处理，即拥有单独的审计表，但将其构建为伪物化视图以呈现应用程序的组合视图。
For me, this has the advantage of having a simple "current" view and a separate but completely automated "audit" view (which in this case also has the current view).
对我来说，这具有一个简单的“当前”视图和一个单独但完全自动化的“审计”视图（在这种情况下也有当前视图）的优点。
Something like:
就像是：
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
回答by DCookie
If you want to develop a generic solution, you might want to take a look at DBMS_SQL package. With it you could develop a package/procedure that takes a table name as input and builds the updates based on that, by examining the table structure in the dictionary and building the updates on the fly. It would be non trivial up-front development, but a lot less maintenance in the future, since if a table structure changes, the code would sense that and adapt. This method would work for any table that you care to use it with.
如果你想开发一个通用的解决方案，你可能想看看 DBMS_SQL 包。有了它，您可以开发一个包/过程，通过检查字典中的表结构并动态构建更新，将表名作为输入并基于它构建更新。这将是非常重要的前期开发，但未来的维护要少得多，因为如果表结构发生变化，代码会感觉到并适应。此方法适用于您想使用它的任何表。
回答by dpbradley
Depending on the complexity of your database (number of tables, size, depth of PK/FK relationships, other logic in triggers), you might want to look at Oracle Workspace Management. You make an API call to put a table under workspace management which results in Oracle replacing the table with an updatable view and other corresponding objects that maintain a history of all versions of the rows.
根据数据库的复杂性（表的数量、大小、PK/FK 关系的深度、触发器中的其他逻辑），您可能需要查看Oracle 工作区管理。您进行 API 调用以将表置于工作区管理之下，这会导致 Oracle 用可更新视图和其他相应对象替换该表，这些对象维护所有行版本的历史记录。
I've used this and while there are disadvantages, one advantage for auditing is that the code objects are all generated by Oracle and their correctness is generally assumed.
我已经使用了它，虽然有缺点，但审计的一个优点是代码对象都是由 Oracle 生成的，并且通常假设它们的正确性。
回答by Jeffrey Kemp
The only time I might recommend that historical records be stored in the same table as the "current" records is when FK links to the records must or might need to link to them. For example, one application I've seen had some FK links that would link to the record as of a "point in time", that is, if the record was updated, the FK would still link to the historical record - this was an important part of the design and separating history records into a second table would have made it more unwieldy.
我可能建议将历史记录与“当前”记录存储在同一个表中的唯一时间是当 FK 链接到记录必须或可能需要链接到它们时。例如，我见过的一个应用程序有一些 FK 链接，这些链接会链接到“时间点”的记录，也就是说，如果记录被更新，FK 仍然会链接到历史记录 - 这是一个设计的重要部分并将历史记录分离到第二个表中会使其更加笨拙。
Apart from that, I'd prefer that a business requirement for tracking all changes should be solved using a separate "history" table for each table. Sure, it means more DDL, but it simplifies the application code enormously and you'll also benefit from better performance and scalability.
除此之外，我更愿意为每个表使用单独的“历史”表来解决跟踪所有更改的业务需求。当然，这意味着更多的 DDL，但它极大地简化了应用程序代码，您还将受益于更好的性能和可伸缩性。
