[ACCEPTED]-PL SQL trigger to insert history record when a column is updated-triggers

Accepted answer
Score: 11

Assuming a regular table rather than an 56 object table, you don't have a whole lot 55 of options. Your trigger would have to 54 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 53 the COLUMN1, COLUMN2, ... COLUMN<<n>> strings from the data dictionary 52 (USER_TAB_COLS) rather than hard-coding them but you'd 51 still have to hard-code the references to 50 the columns in the :new pseudo-record.

You could 49 potentially write a piece of code that generated 48 the trigger above by querying the data dictionary 47 (USER_TAB_COLS or ALL_TAB_COLS most likely), building a string with 46 the DDL statement, and then doing an EXECUTE IMMEDIATE to 45 execute the DDL statement. You'd then have 44 to call this script any time a new column 43 is added to any table to re-create the trigger 42 for that column. It's tedious but not particularly 41 technically challenging to write and debug 40 this sort of DDL generation code. But it 39 rarely is worthwhile because someone inevitably 38 adds a new column and forgets to re-run 37 the script or someone needs to modify a 36 trigger to do some additional work and it's 35 easier to just manually update the trigger 34 than to modify and test the script that 33 generates the triggers.

More generally, though, I 32 would question the wisdom of storing data 31 this way. Storing one row in the history 30 table for every column of every row that 29 is modified makes using the history data 28 very challenging. If someone wants to know 27 what state a particular row was in at a 26 particular point in time, you would have 25 to join the history table to itself N times 24 where N is the number of columns in the 23 table at that point in time. That's going 22 to be terribly inefficient which very quickly 21 is going to make people avoid trying to 20 use the history data because they can't 19 do useful stuff with it in a reasonable 18 period of time without tearing their hair 17 out. It's generally much more effective 16 to have a history table with the same set 15 of columns that the live table has (with 14 a few more added for tracking dates and 13 the like) and to insert one row in the history 12 table each time the row is updated. That 11 will consume more space but it is generally 10 much easier to use.

And Oracle has a number 9 of ways to audit data changes-- you can 8 AUDIT DML, you can use fine-grained auditing 7 (FGA), you can use Workspace Manager, or 6 you can use Oracle Total Recall. If you 5 are looking for more flexibility than writing 4 your own trigger code, I'd strongly suggest 3 that you investigate these other technologies 2 which are inherently much more automatic 1 rather than trying to develop your own architecture.

Score: 4

You might setup the history table to be 4 the SAME as the main table, + a date and 3 type field. You only need to capture the 2 old values, as the new values are in the 1 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;
/
Score: 1

As a note, depending on your design, if 4 space is a limit, you can create a view 3 that would track the changes in the way 2 you were going for, and just show what the 1 record was at the time.

More Related questions