Oracle RDBMS Audit column trigger formula - with APEX support and serial UID surrogate PK

standard_audit_pk_apex_BIU_table_trigger.sql

-- Prefer Oracle APEX user identity, but gracefully "descend" to lower contexts. Also works pretty well over DB links (avoiding static proxy user identity)!
-- Note the regex call might have some performance implications for very high transaction rates.
create or replace TRIGGER "SCHEMA"."TABLENAME_BIU" 
BEFORE INSERT or UPDATE on TABLENAME FOR EACH ROW
DECLARE
BEGIN
    if :new.ENTITY_UID is null then
        :new.ENTITY_UID := TABLENAME_ENTITY_UID_SEQ.nextval;
    end if;
    :new.AUTH_ID := UPPER(:new.AUTH_ID);
    :new.LAST_UPDATE_DATE := sysdate;
    :new.LAST_UPDATED_BY  := coalesce(
              sys_context('APEX$SESSION','app_user')
             ,regexp_substr(sys_context('userenv','client_identifier'),'^[^:]*')
             ,sys_context('userenv','session_user')
         );
    if INSERTING then
        :new.CREATION_DATE := sysdate;
        :new.CREATED_BY    := coalesce(
              sys_context('APEX$SESSION','app_user')
             ,regexp_substr(sys_context('userenv','client_identifier'),'^[^:]*')
             ,sys_context('userenv','session_user')
         );
        insert into SUPPLEMENTAL_AUID_DETAIL_LOGTABLE (ACTION,ACTIVITY_DATE,COMPUTER_NAME,USER_NAME)
            values('User authorization added: '||:new.AUTH_ID||' ('||:new.AUTHORIZATIONS||')',sysdate,sys_context('USERENV', 'HOST'), sys_context('USERENV', 'OS_USER'))
        ;
    end if;
END;