Oracle DML audit trail

1. Introduction

This project describes how to capture changes so that they can be displayed in APEX as shown in the screen below:

To get started, you can jump straight to the tutorial.

2. How it works

For the tables you want to audit, you will generate a trigger. The trigger captures the changes to the audit tables.

2.1. Generate trigger

For the well-known EMP table, the generated trigger looks like:

create or replace TRIGGER trg_aud_emp
AFTER INSERT OR UPDATE OR DELETE ON emp
FOR EACH ROW
--
-- This trigger was generated by running audit_pkg.generate_trigger('EMP');
--
DECLARE
   l_dml        varchar2(1) := case when updating then 'U' when inserting then 'I' else 'D' end;
   l_trx_id     AUD_TRANSACTIONS.id%TYPE;
   l_table_key  AUD_TRANSACTIONS.table_key%TYPE;
   l_prc_name   varchar2(50) := 'TRG_AUD_emp: ';

BEGIN
   if l_dml = 'D' then
       l_table_key := to_char(:OLD.empno);
   else
       l_table_key := to_char(:NEW.empno);
   end if;
   l_trx_id := audit_pkg.log_transaction('EMP', l_table_key, l_dml);

   if deleting then
      audit_pkg.log_column_change(l_trx_id, 'COMM',     :OLD.comm,     NULL);
      audit_pkg.log_column_change(l_trx_id, 'DEPTNO',   :OLD.deptno,   NULL);
      audit_pkg.log_column_change(l_trx_id, 'EMPNO',    :OLD.empno,    NULL);
      audit_pkg.log_column_change(l_trx_id, 'ENAME',    :OLD.ename,    NULL);
      audit_pkg.log_column_change(l_trx_id, 'HIREDATE', :OLD.hiredate, NULL);
      audit_pkg.log_column_change(l_trx_id, 'JOB',      :OLD.job,      NULL);
      audit_pkg.log_column_change(l_trx_id, 'MGR',      :OLD.mgr,      NULL);
      audit_pkg.log_column_change(l_trx_id, 'SAL',      :OLD.sal,      NULL);
   else
      audit_pkg.log_column_change(l_trx_id, 'COMM',     :OLD.comm,     :NEW.comm);
      audit_pkg.log_column_change(l_trx_id, 'DEPTNO',   :OLD.deptno,   :NEW.deptno);
      audit_pkg.log_column_change(l_trx_id, 'EMPNO',    :OLD.empno,    :NEW.empno);
      audit_pkg.log_column_change(l_trx_id, 'ENAME',    :OLD.ename,    :NEW.ename);
      audit_pkg.log_column_change(l_trx_id, 'HIREDATE', :OLD.hiredate, :NEW.hiredate);
      audit_pkg.log_column_change(l_trx_id, 'JOB',      :OLD.job,      :NEW.job);
      audit_pkg.log_column_change(l_trx_id, 'MGR',      :OLD.mgr,      :NEW.mgr);
      audit_pkg.log_column_change(l_trx_id, 'SAL',      :OLD.sal,      :NEW.sal);
   end if;

EXCEPTION
   when others then
      raise_application_error (-20000, 'Error ' || l_prc_name || to_char (sqlcode) || ' - ' || sqlerrm);
END trg_aud_emp;

Each change is captured in two central tables: AUD_TRANSACTIONS and AUD_TRANSACTION_DATA.

2.2. Create View

To make the audit data more readable for your application, you will manually author a view for each table you are auditing.

The view has the following purposes:

Translate column names to application column labels
Translate FK values to functional values
Translate usernames to natural names

For the EMP table, this view could look like this:

emp_aud_vw.sql

create or replace view emp_aud_vw as
select trx.table_key
, trx.client_identifier as changed_by
, trx.tstamp as changed

-- Map column names to field names
, decode( trd.column_name
        , 'DEPTNO', 'Department'
        , 'ENAME', 'Name'
        , 'JOB', 'Job'
        , 'MGR', 'Manager'
        , 'SAL', 'Salary'
        , trd.column_name) as field

-- Display OLD values
, CASE trd.column_name
    WHEN 'DEPTNO' THEN dpt_old.dname
    WHEN 'MGR' THEN emp_old.ename
    ELSE to_char(trd.old_value)
  END as old_value

-- Display NEW values
, CASE trd.column_name
    WHEN 'DEPTNO' THEN dpt_new.dname
    WHEN 'MGR' THEN emp_new.ename
    ELSE to_char(trd.new_value)
  END as new_value

FROM aud_transaction_data trd
INNER JOIN aud_transactions trx ON trd.trx_id = trx.id

-- Foreign key to Department
LEFT JOIN dept dpt_old ON trd.column_name='DEPTNO' and trd.old_num_value = dpt_old.deptno
LEFT JOIN dept dpt_new ON trd.column_name='DEPTNO' and trd.new_num_value = dpt_new.deptno

-- Foreign key to Employees
LEFT JOIN emp emp_old ON trd.column_name='MGR' and trd.old_num_value = emp_old.empno
LEFT JOIN emp emp_new ON trd.column_name='MGR' and trd.new_num_value = emp_new.empno

WHERE  trx.table_name = 'EMP'
and trx.dml='U'

The view only includes the UPDATE changes, because these are all we want to see on the APEX page.

2.3. Create a Page Region for the Audit View

See the tutorial for a description of these steps.

2.4. Handling changes to the data model

Any time you change the table definition (add/remove a column) you must re-run the trigger generator. Possibly you may also want to change your view definition.

3. Installation Steps

The source for this project can be found on github. To install this package in your APEX project, follow these steps.

Navigate to SQL Workshop > SQL Scripts and perform the following steps:

Click on Upload.
Choose file tables.sql
Click on Upload
Click on Run

Repeat for audit_pkg_pks.sql and audit_pkg_pkb.sql.

Navigate to SQL Workshop > SQL Commands. Copy & paste this script:

begin
  audit_pkg.generate_trigger(:table_name);
end;

Click on the Save button and enter name 'Create Audit Trigger'. It will now be readily available for future use under Saved SQL.

4. Tutorial

First follow the installation steps described previously.

4.1. Install demo tables

In APEX, navigate to SQL Workshop > Utilities > Sample Datasets.

Install HR Data. This will install several tables and views, each prefixed by OEHR_. We will be basing the example on table OEHR_EMPLOYEES.

To make it a little more interesting, we’ll add a multi-line Notes field:

alter table OEHR_EMPLOYEES add
notes varchar2(2000);

4.2. Create the audit trigger

Navigate to SQL Workshop > SQL Commands.

On the lower half of the screen, select Saved SQL.
Click on Create Audit Trigger and click on Run. A prompt appears for the table name
Enter OEHR_EMPLOYEES and click on Submit.

To view the generated trigger:

navigate to SQL Workshop > Object Browser
Click on table OEHR_EMPLOYEES
Click on Triggers (on the right)
Click on trigger TRG_AUD_OEHR_EMPLOYEES
Click on Code

4.3. Create the view

create or replace view oehr_employees_aud_vw as
select trx.table_key
, trx.client_identifier as changed_by
, trx.tstamp as changed

-- Map column names to field names
, decode( trd.column_name
        , 'DEPARTMENT_ID', 'Department'
        , 'JOB_ID', 'Job'
        , 'MANAGER_ID', 'Manager'
        , 'SAL', 'Salary'
        , trd.column_name) as field

-- Display OLD values
, CASE trd.column_name
    WHEN 'DEPARTMENT_ID' THEN dpt_old.department_name
    WHEN 'MANAGER_ID' THEN emp_old.first_name || ' ' || emp_old.last_name
    WHEN 'JOB_ID' THEN job_old.job_title
    ELSE to_char(trd.old_value)
  END as old_value

-- Display NEW values
, CASE trd.column_name
    WHEN 'DEPARTMENT_ID' THEN dpt_new.department_name
    WHEN 'MANAGER_ID' THEN emp_new.first_name || ' ' || emp_new.last_name
    WHEN 'JOB_ID' THEN job_new.job_title
    ELSE to_char(trd.new_value)
  END as new_value

FROM aud_transaction_data trd
INNER JOIN aud_transactions trx ON trd.trx_id = trx.id

-- Foreign key to OEHR_DEPARTMENTS
LEFT JOIN OEHR_DEPARTMENTS dpt_old ON trd.column_name='DEPARTMENT_ID' and trd.old_num_value = dpt_old.department_id
LEFT JOIN OEHR_DEPARTMENTS dpt_new ON trd.column_name='DEPARTMENT_ID' and trd.new_num_value = dpt_new.department_id

-- Foreign key to OEHR_JOBS, note that this is a varchar FK
LEFT JOIN OEHR_JOBS job_old ON trd.column_name='JOB_ID' and to_char(trd.old_value) = job_old.job_id
LEFT JOIN OEHR_JOBS job_new ON trd.column_name='JOB_ID' and to_char(trd.new_value) = job_new.job_id

-- Foreign key to OEHR_EMPLOYEES
LEFT JOIN OEHR_EMPLOYEES emp_old ON trd.column_name='MANAGER_ID' and trd.old_num_value = emp_old.employee_id
LEFT JOIN OEHR_EMPLOYEES emp_new ON trd.column_name='MANAGER_ID' and trd.new_num_value = emp_new.employee_id

WHERE  trx.table_name = 'OEHR_EMPLOYEES'
and trx.dml='U'

4.4. Create Page

4.4.1. Create standard Page

In your application:

select the Create Page button
Choose Form > Report with Form. Click Next.
Fill out Report Page name and Form Page Name. Click Next.
Create a new navigation menu entry
Datasource: Select table OEHR_EMPLOYEES. Click Next.
Create Form: Select EMPLOYEE_ID as the primary key. Press Create.

Let’s add LOVs to the 3 foreign keys. Set the Type to Select List and List of Values Type as SQL Query with the following SQL:

JOB_ID

select job_title as display_value
, job_id as return_value
from oehr_jobs
order by 1

MANAGER_ID

select email as display_value
, employee_id as return_value
from oehr_employees
order by 1

DEPARTMENT_ID

select department_name as display_value
, department_id as return_value
from oehr_departments
order by 1

We now have the regular Report and Form. We’ll now add a region with the audit information.

4.4.2. Add Interactive Report

Add an Interactive Report Region
Title: Changes
Source > Table Name: oehr_employees_aud_vw
Where-clause: table_key = :P3_EMPLOYEE_ID
Select column TABLE_KEY and make it type Hidden Column
Select column CHANGED and set the format mask to YYYY-MM-DD HH24:MI:SS
Select column OLD_VALUE and set the HTML Expression to <pre style="white-space:pre-wrap">#OLD_VALUE#</pre>
Select column NEW_VALUE and set the HTML Expression to <pre style="white-space:pre-wrap">#NEW_VALUE#</pre>

NOTE

the HTML expressions ensure that any newline characters render as a new line.

Run the report

Actions > Format > Control Break
Select columns Changed and Changed By
Actions > Data > Sort
Select Changed Ascending
Actions > Report > Save Report
As Default Report Settings, Primary

Go back to the Report Designer

Select the Interactive Report named Changes and click on the Attributes header on the right
Search Bar > Include Search Bar: Disable.

To hide the row with aggregation/group by “filter boxes” above the report:

Select the report region in the Page Designer, set the Advanced > Static ID to an identifier, e.g. CHANGES. This is so that the CSS only affects this region.

Select the page and set the CSS > Inline attribute to

/* Hide the header row with the Group By boxes */
#CHANGES .a-IRR-controlsContainer {
   display: none;
}

Save the page

To make the Changes region collapsed (so that it does not show immediately):

Select the Changes region
Set Appearance > Template to Collapsible
Click on the Template Options and set Default State to Collapsed
Save the page

5. Design Notes

5.1. Data Model

The changes for all tables are captured in these two tables:

5.1.1. Date and timezone

We want the time to be displayed in the Users local time. There are two components to this:

Use the TIMESTAMP WITH LOCAL TIME ZONE datatype. This stores the timestamp normalized to server time. When users retrieve the data, Oracle Database returns it in the users' local session time zone. See also Database documentation
Go to Shared Components > Globalisation > Globalization Attributes. Enable Automatic Time Zone. This sets the timezone based on the users browser. See more information in the Apex Documentation

5.1.2. Change Columns

The OLD and NEW values are captured as CLOB and the numeric values are also stored in NUMERIC columns. The numeric columns make it easier and more performant to join with lookup tables.

5.1.3. Excluding Audit columns

You will often have Audit columns on the table. These can be excluded from auditing. See the package code.

5.1.4. Interactive Report or Interactive Grid

The Interactive Grid has several drawbacks compared to an Interactive Report:

The headers do not repeat making it less readable
The rows have equal height making it difficult (impossible?) to handle multiline field values
There is no easy way to render newlines as new lines.

These steps were performed to create the region shown above:

Add an Interactive Grid Region
Title: Changes
Source > Table Name: oehr_employees_aud_vw
Where-clause: table_key = :P3_EMPLOYEE_ID
Select column TABLE_KEY and make it type Hidden Column
Select column CHANGED and set the format mask to YYYY-MM-DD HH24:MI:SS

Run the report

Actions > Format > Control Break
Select columns Changed (Ascending) and Changed By
Actions > Report > Save

Go back to the Report Designer

Select the Interactive Grid named Changes and click on the Attributes header on the right
Toolbar: Disable.

6. Credits

This design is based on a blogpost by Michelle Skamene.

It also took inspiration from Connor McDonald’s blog post, with source on github.

This site is published from an Asciidoc source using the instructions provided here.