Re: system trigger question

From: <fitzjarrell_at_cox.net>
Date: 18 Apr 2006 17:42:05 -0700
Message-ID: <1145407325.889620.234070@z34g2000cwc.googlegroups.com>

Comments embedded.
epipko_at_gmail.com wrote:
> Hi all,
> Why doesn't the following work?

You can't commit in a trigger. And LAST_MODULE and LAST_PROGRAM are not columns in V$SESSION.

> ----------------------------
> 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;
> /
> ---------------------------------------------
>

The above should be:

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,program,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;

exception when others then raise;
END;
/

Notice the commit is missing, and the proper columns are selected from V$SESSION.
> 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

David Fitzjarrell Received on Tue Apr 18 2006 - 19:42:05 CDT