Re: system trigger question

Comments again embedded.
fitzjarrell_at_cox.net wrote:
> 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);
> >

I neglected to mention this trigger is invalid as it's missing:

end;
/

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

Making the changes I suggest put these triggers into working order.

David Fitzjarrell Received on Tue Apr 18 2006 - 19:45:13 CDT