SQL PL SQL触发器在列更新时插入历史记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9281447/
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
PL SQL trigger to insert history record when a column is updated
提问by Zack Macomber
I would like to insert a row into a history table when anycolumn is updated in a table.
当表中的任何列更新时，我想在历史表中插入一行。
I'm just looking to capture the column name, old value and new value.
我只是想捕获列名、旧值和新值。
I'd like this trigger to be as reusable as possible as I'm going to use the same concept on other tables.
我希望这个触发器尽可能可重用，因为我将在其他表上使用相同的概念。
I'm familiar with triggers and with how to capture updates on one column. I'm specifically looking for how to write onetrigger that inserts a record into a history table for anycolumn that gets updated in the history table's corresponding table.
我熟悉触发器以及如何捕获一列上的更新。我正在专门寻找如何编写一个触发器，将记录插入到历史表的任何列的历史表中，该列在历史表的相应表中得到更新。
EDIT 1
I have stated NOWHEREin my post that I'm looking for source code so shame on anyone that downvotes me and thinks that I'm looking for that. You can check my previous questions/answers to see I'm not one looking for "free source code".
编辑1
我已经指出NOWHERE在我的职位，我在寻找源代码，这样任何人耻辱，downvotes我，认为我在找那个。您可以查看我之前的问题/答案，看看我不是在寻找“免费源代码”。
As I stated in my original question, I'm looking for howto write this. I've examined http://plsql-tutorial.com/plsql-triggers.htmand there's a code block which shows how to write a trigger for when ONE column is updated. I figured that maybe someone would have the know-how to give direction on having a more generic trigger for the scenario I've presented.
正如我在最初的问题中所说，我正在寻找如何写这个。我已经检查了http://plsql-tutorial.com/plsql-triggers.htm并且有一个代码块，它显示了如何为更新一列时编写触发器。我想也许有人会知道如何为我提出的场景提供更通用的触发器。
回答by Justin Cave
Assuming a regular table rather than an object table, you don't have a whole lot of options. Your trigger would have to be something of the form
假设一个普通表而不是一个对象表，你没有很多选择。你的触发器必须是某种形式
CREATE OR REPLACE TRIGGER trigger_name
AFTER UPDATE ON table_name
FOR EACH ROW
BEGIN
IF( UPDATING( 'COLUMN1' ) )
THEN
INSERT INTO log_table( column_name, column_value )
VALUES( 'COLUMN1', :new.column1 );
END IF;
IF( UPDATING( 'COLUMN2' ) )
THEN
INSERT INTO log_table( column_name, column_value )
VALUES( 'COLUMN2', :new.column2 );
END IF;
<<repeat for all columns>>
END;
You could fetch the COLUMN1, COLUMN2, ... COLUMN<<n>>strings from the data dictionary (USER_TAB_COLS) rather than hard-coding them but you'd still have to hard-code the references to the columns in the :newpseudo-record.
您可以从数据字典 ( ) 中获取COLUMN1, COLUMN2, ...COLUMN<<n>>字符串，USER_TAB_COLS而不是对它们进行硬编码，但您仍然必须对:new伪记录中的列的引用进行硬编码。
You could potentially write a piece of code that generated the trigger above by querying the data dictionary (USER_TAB_COLSor ALL_TAB_COLSmost likely), building a string with the DDL statement, and then doing an EXECUTE IMMEDIATEto execute the DDL statement. You'd then have to call this script any time a new column is added to any table to re-create the trigger for that column. It's tedious but not particularly technically challenging to write and debug this sort of DDL generation code. But it rarely is worthwhile because someone inevitably adds a new column and forgets to re-run the script or someone needs to modify a trigger to do some additional work and it's easier to just manually update the trigger than to modify and test the script that generates the triggers.
您可能会编写一段代码，通过查询数据字典（USER_TAB_COLS或ALL_TAB_COLS最有可能），使用 DDL 语句构建一个字符串，然后执行 anEXECUTE IMMEDIATE来执行DDL 语句，从而生成上面的触发器。然后，每当向任何表添加新列时，您都必须调用此脚本以重新创建该列的触发器。编写和调试此类 DDL 生成代码很乏味，但在技术上并不是特别具有挑战性。但很少值得这样做，因为有人不可避免地添加了一个新列而忘记重新运行脚本，或者有人需要修改触发器来做一些额外的工作，而且手动更新触发器比修改和测试生成的脚本更容易触发器。
More generally, though, I would question the wisdom of storing data this way. Storing one row in the history table for every column of every row that is modified makes using the history data very challenging. If someone wants to know what state a particular row was in at a particular point in time, you would have to join the history table to itself N times where N is the number of columns in the table at that point in time. That's going to be terribly inefficient which very quickly is going to make people avoid trying to use the history data because they can't do useful stuff with it in a reasonable period of time without tearing their hair out. It's generally much more effective to have a history table with the same set of columns that the live table has (with a few more added for tracking dates and the like) and to insert one row in the history table each time the row is updated. That will consume more space but it is generally much easier to use.
不过，更一般地说，我会质疑以这种方式存储数据是否明智。为修改的每一行的每一列在历史表中存储一行使得使用历史数据非常具有挑战性。如果有人想知道特定行在特定时间点处于什么状态，您必须将历史表与其自身连接 N 次，其中 N 是该时间点表中的列数。这将是非常低效的，这很快就会使人们避免尝试使用历史数据，因为他们无法在合理的时间内用它做有用的事情而不费心思。它' 使用与实时表具有相同列集的历史表（添加一些用于跟踪日期等）并在每次更新行时在历史表中插入一行通常会更有效。这将占用更多空间，但通常更易于使用。
And Oracle has a number of ways to audit data changes-- you can AUDITDML, you can use fine-grained auditing (FGA), you can use Workspace Manager, or you can use Oracle Total Recall. If you are looking for more flexibility than writing your own trigger code, I'd strongly suggest that you investigate these other technologies which are inherently much more automatic rather than trying to develop your own architecture.
而且 Oracle 有多种审计数据更改的方法——AUDIT您可以使用 DML、您可以使用细粒度审计 (FGA)、您可以使用 Workspace Manager，或者您可以使用 Oracle Total Recall。如果您正在寻找比编写自己的触发器代码更多的灵活性，我强烈建议您研究这些其他技术，这些技术本质上更加自动化，而不是尝试开发自己的架构。
回答by tbone
You might setup the history table to be the SAME as the main table, + a date and type field. You only need to capture the old values, as the new values are in the main table.
您可以将历史表设置为与主表相同，+ 日期和类型字段。您只需要捕获旧值，因为新值在主表中。
try this (untested):
试试这个（未经测试）：
create or replace trigger "MY_TRIGGER"
before update or delete
on MY_TABLE referencing new as new old as old
for each row
declare
l_dml_type varchar2(10);
begin
if (updating) then
l_dml_type := 'UPD';
else
l_dml_type := 'DEL';
end if;
insert into MY_TABLE_HIST
(
col1,
col2,
col3,
dml_type,
dml_date
)
values
(
:old.col1,
:old.col2,
:old.col3,
l_dml_type,
sysdate
);
end;
/
回答by Michael B
As a note, depending on your design, if space is a limit, you can create a view that would track the changes in the way you were going for, and just show what the record was at the time.
请注意，根据您的设计，如果空间有限，您可以创建一个视图来跟踪您想要的方式的变化，并只显示当时的记录。
