Check Logon and logoff trigger on Database

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.