PL SQL触发器，用于在更新列时插入历史记录 [英] PL SQL trigger to insert history record when a column is updated

问题描述

当表中的任何列更新时，我想在历史记录表中插入一行.

I would like to insert a row into a history table when any column 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.

我熟悉触发器以及如何捕获某一列上的更新.我正在专门寻找如何编写 one 触发器，该触发器将一条记录插入到历史记录表的 any 列中，该列在历史记录表的对应表中得到更新.

I'm familiar with triggers and with how to capture updates on one column. I'm specifically looking for how to write one trigger that inserts a record into a history table for any column that gets updated in the history table's corresponding table.

编辑1
我在帖子中指出了 NOWHERE ，我正在寻找源代码，因此对那些低估我并认为我在寻找源代码的人感到羞耻.您可以查看我以前的问题/答案，以了解我不是在寻找免费源代码"的人.

EDIT 1
I have stated NOWHERE in 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".

正如我在最初的问题中所述，我正在寻找如何编写此内容.我已经检查过 http://plsql-tutorial.com/plsql-triggers.htm 还有一个代码块，显示了如何在ONE列更新时编写触发器.我认为也许有人会为我介绍的场景提供一个更通用的触发器的专业知识.

As I stated in my original question, I'm looking for how to write this. I've examined http://plsql-tutorial.com/plsql-triggers.htm and 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.

推荐答案

假定常规表而不是对象表，则没有很多选择.您的触发器必须是某种形式的

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;

您可以从数据字典(USER_TAB_COLS)中获取COLUMN1，COLUMN2，... COLUMN<<n>>字符串，而不是对其进行硬编码，但是您仍然必须对引用进行硬编码. :new伪记录中的列.

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 :new pseudo-record.

通过查询数据字典(最有可能是USER_TAB_COLS或ALL_TAB_COLS)，使用DDL语句构建字符串，然后执行EXECUTE IMMEDIATE来执行，您可能会编写一段生成上述触发器的代码. DDL语句.然后，每当将新列添加到任何表中时，您都必须调用此脚本，以重新创建该列的触发器.编写和调试这种DDL生成代码很繁琐，但在技术上并不是特别困难.但这很少值得，因为有人会不可避免地添加新列并忘记重新运行脚本，或者有人需要修改触发器来做一些额外的工作，而仅手动更新触发器比修改和测试生成的脚本要容易得多.触发器.

You could potentially write a piece of code that generated the trigger above by querying the data dictionary (USER_TAB_COLS or ALL_TAB_COLS most likely), building a string with the DDL statement, and then doing an EXECUTE IMMEDIATE to 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.

不过，更笼统地说，我会质疑以这种方式存储数据的智慧.在历史记录表中为修改后的每一行的每一列存储一行，这使得使用历史记录数据非常具有挑战性.如果某人想知道特定行在特定时间点处的状态，则必须将历史记录表自身连接N次，其中N是该时间点表中的列数.这将是非常低效的，很快就会使人们避免尝试使用历史数据，因为他们无法在合理的时间内使用有用的东西而不会扯掉头发.通常，使用一个具有与活动表相同的列集的历史记录表(添加一些用于跟踪日期等)的历史记录表，并在每次更新该行时在历史记录表中插入一行会更有效.这样会占用更多空间，但通常更容易使用.

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.

Oracle提供了多种方法来审核数据更改-您可以AUDIT DML，可以使用细粒度审核(FGA)，可以使用Workspace Manager或可以使用Oracle Total Recall.如果您要寻找比编写自己的触发代码更多的灵活性，那么我强烈建议您研究这些本身具有更高自动化性的其他技术，而不是尝试开发自己的体系结构.

And Oracle has a number of ways to audit data changes-- you can AUDIT DML, 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.

这篇关于PL SQL触发器，用于在更新列时插入历史记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！