Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2002
    Posts
    78

    Unanswered: system events triggers

    Hai

    I Want to audit my database using system events trigger .The below article i got from " by Donald K. Burleson".The proble is After creating trigger at sys account ,its not connected to any schemas giving "ILLEGAL ATTRIBUTE" and below message.Its connected to another schemas from sys account if dropped that trigger.How to overcome this problem without dropping trigger

    It would be appreciate any help

    Thanks in advance
    mohan


    sql>conn perfstat/perfstat;


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


    sql> conn internal;

    create or replace trigger logoff_audit before logoff on database
    begin
    update perfstat.stats_user_log set last_program=(select program from v$session where sys_context
    ('USERENV','SESSIONID')=audsid) where sys_context('USERENV','SESSIONID')=session_id;
    update perfstat.stats_user_log set last_action=(select action from v$session where sys_context('USERENV','SESSIONID')
    =audsid) where sys_context('USERENV','SESSIONID')=session_id;

    update perfstat.stats_user_log set last_module=(select module from v$session where sys_context('USERENV','SESSIONID')=audsid)
    where sys_context('USERENV','SESSIONID')=session_id;

    update perfstat.stats_user_log set logoff_day=sysdate where sys_context('USERENV','SESSIONID')=session_id;

    update perfstat.stats_user_log set logoff_time=to_char(sysdate,'hh24:mi:ss') where sys_context('USERENV','SESSIONID')=session_id;

    update perfstat.stats_user_log set elapsed_minutes=round((logoff_day - logon_day)*1440) where sys_context('USERENV','SESSIONID')=session_id;
    commit;
    end;



    SQL> conn perfstat/perfstat;
    ERROR:
    ORA-00604: error occurred at recursive SQL level 1
    ORA-01427: single-row subquery returns more than one row
    ORA-06512: at line 2

  2. #2
    Join Date
    May 2002
    Location
    Thailand
    Posts
    12

    Smile Re: system events triggers

    Hi,

    What i find missing in your trigger is the use of the second expression in the where clause i.e., i mean
    where sys_context('USERENV','SESSIONID')=audsid)
    Include this in the last three update statement and ur trigger should be happy...

    Rama

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •