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.