how to update :APP_USER detail in a field, apex5.0

Question

Any idea how to add :APP_USER in an audit field? Got a form when user input data in a order_comments field and click on apply changes then the audit field userid, datetime should be updated. How to do that?

Answer 1

An age-old question.

The common method to capture the user for audit purposes is to use a before-row trigger on the tables.

For example, with these (common) audit fields:

• CREATION_USER
• CREATION_DT
• LAST_UDPATE_USER
• LAST_UPDATE_DT

You'd have a trigger on the table like this:

CREATE OR REPLACE TRIGGER MY_TABLE_RBIU_AUDIT
    BEFORE INSERT OR UPDATE ON MY_TABLE
    FOR EACH ROW
BEGIN
  IF INSERTING THEN
    :NEW.CREATION_USER := NVL(v('APP_USER'), USER);
    :NEW.CREATION_DT := SYSDATE;
  END IF;

  IF UPDATING THEN
    :NEW.LAST_UPDATE_USER := NVL(v('APP_USER'), USER);
    :NEW.LAST_UPDATE_DT := SYSDATE;
  END IF;
END;
/

If you don't want to use a trigger and are not using an API through which you do this, and for some weird reason want to do this within apex itself (hint: use the trigger or api), you can still opt to set the default value of the item to &APP_USER. (when using "Static Text")