Create logging data in the same table

Andre Crone 3
0 0
Read Time:1 Minute, 57 Second

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:

Happy
Happy
0 %
Sad
Sad
0 %
Excited
Excited
0 %
Sleepy
Sleepy
0 %
Angry
Angry
0 %
Surprise
Surprise
0 %

Average Rating

5 Star
0%
4 Star
0%
3 Star
0%
2 Star
0%
1 Star
0%

3 thoughts on “Create logging data in the same table

  1. What is exactly the business case here? Say we have an entity PERSON, with id, name, et cetera; and an entity STATUS_HISTORY with attributes: id, person_id, status_id, start_date_active, user_id et cetera. Then, status modifications can be easily implemented by simply adding a new record to the status_history table. That’s all. 😉

  2. But why would you want to do this? Seems like you massively complicate all future business logic with this table.

    For example, say you repeat your update… ouch! You’re updating your audit rows too!

  3. you can use dbms_wm.enable_version . use table with version, give more options , less code to write.

Comments are closed.

Next Post

Eindgebruikers zijn Olifanten - Driedaags seminar ‘Effective Oracle by Design’door Oracle-fenomeen Ask-Tom Kyte (article from Optimize, March 2005)

The March 2005 issue of Optimize, the Dutch Oracle Developer Magazine, contains a review of the Tom Kyte seminar AskTom-Live. This seminar has been discussed in many details on this weblog. A summary of the most interesting findings has been included in this article. Unfortunately, this article is in Dutch. […]
%d bloggers like this: