ORACLE APEX CREATE LOGON TRIGGER

Question

I want to update a status to my table with specific criteria (where clause) when any user logs on..

create or replace TRIGGER AUTO_COMPLETE
AFTER LOGON ON SCHEMA
BEGIN
    UPDATE SESSIONS
    SET SESSIONS.STATUS = 5
    WHERE SESSIONS.STATUS =2
    AND TO_CHAR(SESSIONS.SESSION_DATE, 'MM-DD-YYYY HH24.MM') < TO_CHAR (SYSDATE, 'MM-DD-YYYY HH24.MM');
END AUTO_COMPLETE;

Unfortunately this trigger is not fired and i have no errors!!

Answer 1

Apex is stateless, see first paragraph here. That means that a new database session is created for every page render or post process. I think that would make that logon trigger fire constantly when the user navigates around the application.

You could create an application process with point "After Authentication" in your application. That will fire only once for the application after the user authenticates.

Keeping this functionality separate from your database makes sense. A logon trigger will fire when you connect through sqldeveloper or over rest as well - and it will affect performance.