Hi

We want to store in a table the connection time of each session.
I have created a log_info table with these columns

logon_time
username
osuser
program
audit_id
logoff_time

I have created two triggers, one after logon and one for before logoff. I have used audsid for this purpose, the trigger is smth like (I dont have the codes here)


create or replace trigger logon_trg after logon on database
declare
session_rec v$session%rowtype;
begin

select *
into session_rec
from v$session
where audsid=userenv('sessionid');

if
session_rec.program = 'SQLPLUSW.EXE'
then
insert into lsc.log_info
values(sysdate, user, session_rec.osuser, session_rec.program, userenv('sessionid'), null);
end if;
end;
/

create or replace trigger logoff_trg before logoff on database
declare
session_rec v$session%rowtype;
begin

select *
into session_rec
from v$session
where audsid=userenv('sessionid');

if
session_rec.program = 'SQLPLUSW.EXE'
then
update lsc.log_info
set logoff_time=sysdate /*here I could use sysdate-logon_time*/
where audit_id=userenv('sessionid');
end if;
end;
/


I have tried this and it works however I am trying with 3 or 4 sessions only, since this table is not being truncated after long time obviously my audit_id will be duplicated (I am using it here as a candidate key), I need to know if there is another way to obtain the connection of a session

Cheers

[Edited by pando on 06-25-2001 at 04:50 PM]