TRIGGER AFTER LOGON

create or replace trigger trg_no_sys_logon
after logon
on database
declare
v_machine varchar2(30);
v_count number;
begin
select count(machine) into v_count
from host_machine_list
where SYS_CONTEXT('USERENV','HOST') = MACHINE;
If v_count = 0 then
for check_users in(select username
from v$session
where AUDSID = USERENV('SESSIONID')) loop
if upper(check_users.username) in ('SYSTEM')
then
insert into gdemo.dbas_aud3
(
LOGON_TIME
,TERMINAL
,SESSIONID
,INSTANCE
,ENTRYID
,ISDBA
,CURRENT_USER
,CURRENT_USERID
,SESSION_USER
,SESSION_USERID
,PROXY_USER
,PROXY_USERID
,DB_NAME
,HOST
,OS_USER
,EXTERNAL_NAME
,IP_ADDRESS
,NETWORK_PROTOCOL
,AUTHENTICATION_TYPE)
select
SYSDATE
,SYS_CONTEXT('USERENV','TERMINAL') TERMINAL
,SYS_CONTEXT('USERENV','SESSIONID') SESSIONID
,SYS_CONTEXT('USERENV','INSTANCE') INSTANCE
,SYS_CONTEXT('USERENV','ENTRYID') ENTRYID
,SYS_CONTEXT('USERENV','ISDBA') ISDBA
,SYS_CONTEXT('USERENV','CURRENT_USER') CURRENT_USER
,SYS_CONTEXT('USERENV','CURRENT_USERID') CURRENT_USERID
,SYS_CONTEXT('USERENV','SESSION_USER') SESSION_USER
,SYS_CONTEXT('USERENV','SESSION_USERID') SESSION_USERID
,SYS_CONTEXT('USERENV','PROXY_USER') PROXY_USER
,SYS_CONTEXT('USERENV','PROXY_USERID') PROXY_USERID
,SYS_CONTEXT('USERENV','DB_NAME') DB_NAME
,SYS_CONTEXT('USERENV','HOST') HOST
,SYS_CONTEXT('USERENV','OS_USER') OS_USER
,SYS_CONTEXT('USERENV','EXTERNAL_NAME') EXTERNAL_NAME
,SYS_CONTEXT('USERENV','IP_ADDRESS') IP_ADDRESS
,SYS_CONTEXT('USERENV','NETWORK_PROTOCOL') NETWORK_PROTOCOL
,SYS_CONTEXT('USERENV','AUTHENTICATION_TYPE') AUTHENTICATION_TYPE from dual;
end if;
commit;
end loop;
END IF;
exception
when others then
NULL;
end trg_no_sys_logon_3;
/

TRIGGER LOGOFF

Step 1: Logon to the database

Logon to the database using any user such as HR, SH, OE or any other you want.

1	C:\> SQLPLUS hr/hr

Read here: How to connect to the database With Sample User (e.g. HR) using SQL Developer

Step 2: Create a table.

Create a table to dump the data generated by your schema level logoff trigger.

1 2 3 4 5 6 7 8

CREATE TABLE hr_evnt_audit  (   event_type VARCHAR2(30),   logon_date DATE,   logon_time VARCHAR2(15),   logof_date DATE,   logof_time VARCHAR2(15)  );

Step 3: Write the trigger.

Below written trigger will execute every time user HR logs off from the database.

1 2 3 4 5 6 7 8 9 10 11 12 13

CREATE OR REPLACE TRIGGER log_off_audit BEFORE LOGOFF ON SCHEMA BEGIN   INSERT INTO hr_evnt_audit VALUES(     ora_sysevent,     NULL,     NULL,     SYSDATE,     TO_CHAR(sysdate, 'hh24:mi:ss')   );   COMMIT; END; /