system trigger question

From: <epipko_at_gmail.com>
Date: 18 Apr 2006 16:16:10 -0700
Message-ID: <1145402170.091713.11250@i40g2000cwc.googlegroups.com>

Hi all,
Why doesn't the following work?

---

connect sys/pass_at_test as sysdba;

create table stats$user_log
(

   user_id           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)
);

create or replace trigger logon_audit_trigger AFTER LOGON ON DATABASE
BEGIN
insert into stats$user_log
values(user,

       sys_context('USERENV','SESSIONID'),
       sys_context('USERENV','HOST'),
	   null,
	   null,
	   null,
	   sysdate,
	   to_char(sysdate, 'hh24:mi:ss'),
	   null,
	   null,
	   null);

CREATE OR REPLACE TRIGGER logoff_audit_trigger BEFORE LOGOFF ON DATABASE
BEGIN
UPDATE stats$user_log
SET (last_action,last_program,last_module) =

		(SELECT action,last_program,last_module
         FROM v$session
         WHERE sys_context('USERENV','SESSIONID') = audsid),
	logoff_day = SYSDATE,
	logoff_time = TO_CHAR(SYSDATE, 'hh24:mi:ss'),
	elapsed_minutes = ROUND((logoff_day - logon_day)*1440)

WHERE sys_context('USERENV','SESSIONID') = session_id;

COMMIT;
exception when others then raise;
END;
/

---

connect user/pass_at_test;
select * from dual;
exit;

connect sys/pass_at_test as sysdba;
select * from stats$user_log;
*** no rows selected ***

Thanks,
Eugene Received on Tue Apr 18 2006 - 18:16:10 CDT