Re: LOGOFF TRIGGER for getting IP ADDRESS ETC

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Mon, 23 Feb 2004 14:44:20 -0000
Message-ID: <403a11c5$0$7063$ed9e5944@reading.news.pipex.net>

Hi A couple of suggestions

1. Why not recode your trigger as

CREATE OR REPLACE TRIGGER trg_session_info  AFTER LOGON ON DATABASE
BEGIN
 INSERT INTO session_info(
session_id,
client_name,
logon_date,
 work_station,
host_name,
database_server,
db_domain,
auth_type,
ip_address,
network_protocol)
SELECT

SYS_CONTEXT('USERENV', 'SESSIONID') ,
SYS_CONTEXT('USERENV','OS_USER'),
SYS_CONTEXT('USERENV','HOST'),
SYS_CONTEXT('USERENV','DB_NAME'),
SYS_CONTEXT('USERENV','DB_DOMAIN'),
SYS_CONTEXT('USERENV', 'AUTHENTICATION_TYPE'),
SYS_CONTEXT('USERENV', 'IP_ADDRESS'),
SYS_CONTEXT('USERENV','NETWORK_PROTOCOL')

FROM dual;
commit;
END;
/

It would seem to me that it reads better, will do less work on the database and even I can understand it.

2. Create a job to run at midnight (or before your run your report whenever) which deletes rows from your client sessions table that aren't currently connected

-- 
Niall Litchfield
Oracle DBA
Audit Commission UK

Received on Mon Feb 23 2004 - 08:44:20 CST