Oracle APEX user on Oracle Trigger

0
votes

I have an APEX application that when the record is removed it deletes the record from the database. I have a trigger on that table to insert the deleted record into an audit table with the time it was deleted and the username of the person who did the delete. How would I enter the username in the insert to be the user of the APEX application?

2
oracletriggersoracle-apex

2 Answers

2
votes

I can think of 3 ways to get the username from inside a trigger listed below in order of preference:

  1. APEX_APPLICATION.G_USER
  2. SYS_CONTEXT ('APEX$SESSION', 'APP_USER')
  3. v ('APP_USER')

They can each be used with a statement similar to this:

INSERT INTO audit_table (CHANGE_MESSAGE,
                         CHANGED_BY,
                         CHANGED_DATE)
     VALUES ('Some message',
             COALESCE (APEX_APPLICATION.G_USER, USER),
             CURRENT_DATE);

I would highly recommend AGAINST using v('APP_USER') in your trigger as you could potentially see large performance issues. There is a demo APEX application that compares the performance of v('APP_USER') to SYS_CONTEXT ('APEX$SESSION', 'APP_USER') and the results show that it is a significant performance hit to use v('APP_USER') especially in code like a trigger.

0
votes

Try v('APP_USER'), e.g.

insert into audit_table(user, datum) values (v('APP_USER'), sysdate);