Zack Macomber Zack Macomber - 1 month ago 7
SQL Question

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.

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.

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".

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.

Answer

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

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.

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.

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.

Comments