Using audit columns with APEX

It’s good practice to use audit columns in Oracle to see which user created or modified certain records. Application Express is not different.

Filling audit columns is usually done using Before Insert and Before Update triggers on the related tables. These columns usually are Created_Date, Created_User, Modified_Date and Modified_User. Both date columns are filled with Sysdate, but the user columns are a bit different.

In most environments you can suffice with using the database user by setting the column like this:

:NEW.Created_User := USER;

In Application Express applications that’s not enough when you have an authentication scheme based on a user table. In that case you would like to see the application user that inserted or updated the record in you audit.
A simple and smart way to do this is by using the v() construction in your trigger like this:

:NEW.Created_User := nvl(v('APP_USER'),USER);

When you use this kind of code in your triggers, the audit user is filled with the user that was logged into the application. But when the record is altered directly on the database, you will see the database user.

3 thoughts on “Using audit columns with APEX

  1. Another technique:

    I will abstract that call from the trigger into a package.

    ex:
    –specification not shown

    create or replace package body pkg_utils as
    function get_username return varchar2 is
    begin
    return nvl(v(‘APP_USER’),USER);
    — original code extensive to return full name
    — removed for brevity
    end;
    end;

    and use the pkg_utils.get_username function in the trigger.

    This way, if one wanted to code UI using another front-end technique (JSP, ADF, etc) you could extend the function to return the appropriate context user and not touch the trigger !

    – Neelesh

Comments are closed.