Database logoff trigger : Database Level Trigger « Trigger « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Trigger
3. Database Level Trigger

SQL>
SQL> CREATE TABLE session_logon_statistics
  2  (user_logged VARCHAR2(30),
  3  start_time   DATE,
  4  end_time     DATE);

Table created.

SQL>
SQL> CREATE OR REPLACE TRIGGER logoff_log_trigger
  2  BEFORE LOGOFF
  3  ON DATABASE
  4  BEGIN
  5     UPDATE session_logon_statistics
  6     SET    end_time    = SYSDATE
  7     WHERE  user_logged = USER
  8     AND    end_time IS NULL;
  9  END;
 10  /

Trigger created.

SQL>
SQL> SELECT user_logged,
  2         TO_CHAR(start_time, 'MM/DD/YYYY HH24:MI:SS') "START TIME",
  3         TO_CHAR(end_time, 'MM/DD/YYYY HH24:MI:SS') "END TIME"
  4  FROM   session_logon_statistics
  5  where  rownum < 50
  6  order by user_logged, start_time;

no rows selected

SQL>
SQL> drop trigger logoff_log_trigger;

Trigger dropped.

SQL>
SQL> drop table session_logon_statistics;

Table dropped.