Creating LOGON and LOGOFF Triggers

You can create a trigger that performs an action when a user logs on or off the database.

In Example: Creating a LOGON Trigger, a trigger is created to write a record to a log table whenever a user logs on to the HR account. In this example, the user name (USER), the type of activity (LOGON or LOGOFF), current system date (SYSDATE), and the number of employees in the employees table are written to a table. Both SYSDATE and USER are pseudocolumns that return values. See "Using ROWNUM, SYSDATE, and USER Pseudocolumns With SQL".

Creating a LOGON Trigger

-- create a table to hold the data on user logons and logoffs
CREATE TABLE hr_log_table ( user_name VARCHAR2(30), activity VARCHAR2(20), 
                            logon_date DATE, employee_count NUMBER );

-- create a trigger that inserts a record in hr_log_table
-- every time a user logs on to the HR schema
CREATE OR REPLACE TRIGGER on_hr_logon 
  AFTER LOGON  
  ON HR.schema  
DECLARE
  emp_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO emp_count FROM employees; -- count the number of employees
  INSERT INTO hr_log_table VALUES(USER, 'Log on', SYSDATE, emp_count);  
END;
/

In Example: Creating a LOGOFF Trigger, a trigger is created to write a record to a table whenever a user logs off the HR account.

Creating a LOGOFF Trigger

-- create a trigger that inserts a record in hr_log_table
-- every time a user logs off the HR schema
CREATE OR REPLACE TRIGGER on_hr_logoff 
  BEFORE LOGOFF 
  ON HR.schema  
DECLARE
  emp_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO emp_count FROM employees; -- count the number of employees
  INSERT INTO hr_log_table VALUES(USER, 'Log off', SYSDATE, emp_count); 
END;
/

After you log on and log off of the HR account, you can check the hr_log_table to view results of the triggers. For example:

DISCONNECT
CONNECT hr/hr
SELECT * FROM hr_log_table;