-- System triggers to log all logons and logoffs to an internal table
-- Copyright (c) 2004, Caleb.com

-- Create the log table
DROP TABLE cs_log;
CREATE TABLE cs_log (
   ts    DATE,
   usr   VARCHAR2(32),
   evnt  VARCHAR2(20));

-- Trigger to log logons
CREATE OR REPLACE TRIGGER cs_logon
AFTER LOGON ON DATABASE
BEGIN
   -- No need to commit, in fact you can't commit or rollback,
   -- logon/logoff triggers execute in their own transaction
   INSERT INTO cs_log
      VALUES (sysdate, ora_login_user, 'Logon');
END;
/

-- Trigger to log logoffs
CREATE OR REPLACE TRIGGER cs_logoff
BEFORE LOGOFF ON DATABASE
BEGIN
   INSERT INTO cs_log
      VALUES ( sysdate, ora_login_user, 'Logoff');
END;
/

ALTER SESSION SET nls_date_format='dd-mon-yyyy hh:mi:ss';