How do you reference APEX Application Items in Trigger

Question

I am creating a BEFORE DELETE trigger to insert records into an archive table that are deleted through an APEX form on another table. I want to update two columns "UPDATED_BY" and "UPDATED"_DATE" based on the SYSDATE and :APP_USER (APEX Application Item). I'm getting an error related to the :APP_USER (PLS-00049: bad bind variable 'APP_USER'). Is there a specific syntax I have to use to reference APEX app items when creating the trigger?

CREATE OR REPLACE TRIGGER  "ARCHIVE_DELETED_RECORDS" 
BEFORE
delete on "RECORD_TABLE"
for each row
begin
INSERT INTO DELETED_RECORD_TABLE (
    STAGING_ID,
    SOURCE_FILE_ID,
    DELETION_DATE,
    DELETED_BY,
    DELETED_FROM)
SELECT
    STAGING_ID,
    SOURCE_FILE_ID,
    SYSDATE,
    :APP_USER,
    'Record Table 1'
FROM RECORD_TABLE
end;
/
ALTER TRIGGER  "ARCHIVE_DELETED_RECORDS" ENABLE;

Also see this post regarding triggers from 12c svenweller.wordpress.com/2016/02/24/… — Scott

kfinity kfinity · Accepted Answer · 2020-02-18T16:46:35

You can use the V() function to refer directly to APEX bind variables like :APP_USER in a database trigger, or really in any PL/SQL outside of APEX. APEX also generally uses package state to store global variables which need to be accessed externally. See here for some discussion.

In your case, instead of :APP_USER, you could use either V('APP_USER') or APEX_APPLICATION.G_USER. Documentation is here.

Note that if records are deleted from your table outside of APEX (for example, by a DBA running delete from RECORD_TABLE), the DELETED_BY field will be null. So you might want something like:

COALESCE(V('APP_USER'), SYS_CONTEXT ('USERENV', 'OS_USER'), USER)

To prefer the APEX user if that's available, or the OS user if that's available, or finally the current schema user if nothing else is available.

How do you reference APEX Application Items in Trigger

1 Answers