Creating Triggers that Log LOGON and LOGOFF Events

This tutorial shows how to use the CREATE TRIGGER statement to create two triggers, hr_logon_trigger and hr_logoff_trigger. After someone logs on as user HR, hr_logon_trigger adds a row to the table HR_USERS_LOG. Before someone logs off as user HR, hr_logoff_trigger adds a row to the table HR_USERS_LOG.

hr_logon_trigger and hr_logoff_trigger are system triggers. hr_logon_trigger is a BEFORE trigger, and hr_logoff_trigger is an AFTER trigger.

These triggers are not part of the sample application that the tutorials and examples in this document show how to develop and deploy.

To create HR_USERS_LOG, HR_LOGON_TRIGGER, and HR_LOGOFF_TRIGGER:

1. Create the HR_USERS_LOG table:

   CREATE TABLE hr_users_log (
     user_name VARCHAR2(30),
     activity VARCHAR2(20),
     event_date DATE
   );
   

2. Create hr_logon_trigger:

   CREATE OR REPLACE TRIGGER hr_logon_trigger
     AFTER LOGON
     ON HR.SCHEMA
   BEGIN
     INSERT INTO hr_users_log (user_name, activity, event_date)
     VALUES (USER, 'LOGON', SYSDATE);
   END;
   

3. Create hr_logoff_trigger:

   CREATE OR REPLACE TRIGGER hr_logoff_trigger
     BEFORE LOGOFF
     ON HR.SCHEMA
   BEGIN
     INSERT INTO hr_users_log (user_name, activity, event_date)
     VALUES (USER, 'LOGOFF', SYSDATE);
   END;