logon and logoff trigger for tracing user only once after they log on

Hi,
Tracking the user for log on and log off can be done as follows,In this trigger the special part is it will record the user entry only once.The steps which I followed is as follows:

Step 1: Crating user audit table
-------

Note: The below process will only work with sys user only as we are using sys_context so connect as sysdba



Step 1: Crating user audit table
-------
sqlplus
connect sys/manager AS SYSDBA;

create table
stats_user_logon
(
user_id varchar2(30),
osuser varchar2(30),
session_id number(8),
host varchar2(30),
last_program varchar2(48),
last_action varchar2(32),
last_module varchar2(32),
logon_day date,
logon_time varchar2(10),
logoff_day date,
logoff_time varchar2(10),
elapsed_minutes number(8)
)
;
Step 2: Designing a logon trigger

create or replace trigger
logon_audit_trigger
AFTER LOGON ON DATABASE
declare
cnt number;
us varchar2(30);
BEGIN
select user into us from dual;
select count(*) into cnt from stats_user_logon where user_id=us;
if cnt > 0 then
UPDATE stats_user_logon SET LOGON_TIME=to_char(sysdate,'hh24:mi:ss'),LOGON_DAY=SYSDATE where user_id=us;
ELSE
insert into stats_user_logon values(
user,
sys_context('USERENV','OS_USER'),
sys_context('USERENV','SESSIONID'),
sys_context('USERENV','HOST'),
null,
null,
null,
sysdate,
to_char(sysdate,'hh24:mi:ss'),
null,
null,
null
);
END IF;
END;
/

Step 3:Enable triggers for log on
Alter trigger logon_audit_trigger enable;

Step 4:Tracking the users from the below query:
-------

SQL> select user_id||' '||osuser||' '||logon_day||' '||logon_time
from stats_user_logon;
2
USER_ID||''||OSUSER||''||LOGON_DAY||''||LOGON_TIME
--------------------------------------------------------------------------------
T1 oracle 17-DEC-10 18:05:20
T2 oracle 16-DEC-10 18:49:32
TEST1 osuser1 16-DEC-10 18:58:41
TEST2 osuser2 17-DEC-10 18:51:43

That is why PL/SQL is my favourite programming language.



Best regards,

Rafi.