Check Logon and logoff trigger on Database

Check the logon and logoff trigger in the Database
col trigger_name for a20
col status for a10
col triggering_event for a10
select trigger_name,status,triggering_event from user_triggers where triggering_event like ‘LOG%’;

Check the body of trigger

set long 10000
select trigger_body from user_triggers where triggering_event like 'LOG%';


Create logon and logoff Trigger example
Following example is used for auditing purpose when HR schema log-in and log-out.

-- 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;
/

-- 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;
/


Create trigger for set anything specific

CREATE OR REPLACE TRIGGER USER1.LOGONTRG
AFTER LOGON ON DATABASE
WHEN (USER = 'user1')
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA=user2';
END LogonTrg;
/