Create logging data in the same table

Suppose you want to log every update on a table. You want the log records to be in the same original table. You cannot do this in a simple update trigger because this will have a the famous mutating table problem as a result. This post will describe a simple short solution which solves this problem.

Suppose we have a table my_table with two records:

create table my_table(col1 varchar2(2), col2 varchar2(2));
insert into my_table values ('a','a');
insert into my_table values ('b','b');

The trick of the solution lies in three triggers and a package. The package holds a PL/SQL array holding the old values of the records that are updated. The three triggers fill this array (pre update trigger). The post statement trigger reads out these values and inserts them in the table. The package body looks like (we don’t need a package body):

create or replace package my_package
as
  subtype tMyTableRec is my_table%ROWTYPE;
  type tMyTableArray is table of tMyTableRec index by binary_integer;

  gMyTable  tMyTableArray ; -- Global package variable holding an array of my_table records
end;

First we initialise the PL/SQL array. This way we ensure that the variable is properly reset before we issue an update statement:

create or replace trigger before_statement
before update on my_table
begin
  my_package.gMyTable.delete;
end;

Now we can safe all the old values into the array in the pre update row level trigger:

create or replace trigger before_update
before update on my_table
for each row
declare
  lIndex number;
begin
  lIndex := nvl(my_package.gMyTable.last, 0) + 1;

  my_package.gMyTable(lIndex).col1 := :old.col1;
  my_package.gMyTable(lIndex).col2 := :old.col2;
end;

We will now use a post update statement trigger to read out the values of the PL/SQL array. The update is done, so we will not encounter mutation table issues:

create or replace trigger after_statement
after update on my_table
begin
  for lIndex in 1..my_package.gMyTable.count
  loop
    insert into my_table values(my_package.gMyTable(lIndex).col1, my_package.gMyTable(lIndex).col2);
  end loop;

  my_package.gMyTable.delete;
end;

The following proves that the triggers are working. We updated both records. This will result in four records in the table. We logged the old values as new records: