Using audit columns with APEX
Posted in Apex on 11/03/2009 02:30 pm by Michel van ZoestIt’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.
01/05/2012 at 09:32
Why :APP_USER,nvl(v(‘APP_USER’),USER) returns anonymous?
How can resolve it?
12/05/2009 at 10:16
That is indeed a good way to prepare your application for multiple front-ends.
12/05/2009 at 05:57
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