Database logon 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>
SQL>
SQL> CREATE OR REPLACE TRIGGER logon_log_trigger
  2  AFTER LOGON
  3  ON DATABASE
  4  BEGIN
  5     INSERT INTO session_logon_statistics
  6     (user_logged, start_time)
  7     VALUES
  8     (USER, SYSDATE);
  9  END;
 10  /

Trigger created.

SQL>
SQL>
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>
SQL> drop trigger logon_log_trigger;

Trigger dropped.

SQL>
SQL> drop table session_logon_statistics;

Table dropped.