How to write code for logon/logoff triggers in oracle

oracleoracle-11g-r2

I have written the code for a logon/logoff triggers. Logon is working but the logoff trigger is created with an error. Please give some suggestions.

Logging table:

create table logon_off_audit(
user_id varchar2(30),
session_id number(8),
host varchar2(30),
last_program varchar2(48),
last_action varchar(32),
last_module varchar(32),
logon_date date,
logon_time varchar2(30),
logoff_date date,
logoff_time varchar2(10),
elapsed_minutes number(8));

Login trigger:

create or replace trigger
   logon_audit_trigger
after logon on database
begin
insert into logon_off_audit values(
   user,
   sys_context('userenv','sessionid'),
   sys_context('userenv','host'),
   null,
   null,
   null,
   sysdate,
   to_char(sysdate, 'hh24:mi:ss'),
   null,
   null,
   null
);
end;
/

Logoff trigger:

create or replace trigger
   logoff_trigger
before logoff on database
begin
update logon_off_audit set last_action = (select action from v$session where sys_context('userenv','action') = audsid)
where
sys_context('userenv','sessionid') = session_id;
update
logon_off_audit
set
last_program = (select program from v$session where     
sys_context('userenv','program') = audsid)
where
sys_context('userenv','sessionid') = session_id;
update
logon_off_audit
set
last_module = (select module from v$session where     
sys_context('userenv','module') = audsid)
where
sys_context('userenv','sessionid') = session_id;
update
  logon_off_audit
set
   logoff_date = sysdate
where
   sys_context('userenv','sessionid') = session_id;
update
logon_off_audit
set
   logoff_time = to_char(sysdate, 'hh24:mi:ss')
where
   sys_context('userenv','sessionid') = session_id;
update
logon_off_audit
set
elapsed_minutes =     
round((logoff_date - logon_date)*1440)
where
sys_context('userenv','sessionid') = session_id;
end;
/

Best Answer

Basically, all of your sys_context() calls in the subqueries were wrong. It always needs to be sys_context('userenv','session') because you need the sid to join with v$session. Note that you could condense it down to a single update statement.

I've tested this on 12c and it works:

create or replace trigger
   logoff_trigger
before logoff on database
begin
update logon_off_audit set last_action = (select action from v$session where sys_context('userenv','sessionid') = audsid)
where
sys_context('userenv','sessionid') = session_id;
update
logon_off_audit
set
last_program = (select program from v$session where     
sys_context('userenv','sessionid') = audsid)
where
sys_context('userenv','sessionid') = session_id;
update
logon_off_audit
set
last_module = (select module from v$session where     
sys_context('userenv','sessionid') = audsid)
where
sys_context('userenv','sessionid') = session_id;
update
  logon_off_audit
set
   logoff_date = sysdate
where
   sys_context('userenv','sessionid') = session_id;
update
logon_off_audit
set
   logoff_time = to_char(sysdate, 'hh24:mi:ss')
where
   sys_context('userenv','sessionid') = session_id;
update
logon_off_audit
set
elapsed_minutes =     
round((logoff_date - logon_date)*1440)
where
sys_context('userenv','sessionid') = session_id;
end;
/
Related Question