Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2014
    Posts
    1

    Unhappy Unanswered: Log Trail not appearing in log table

    My code is below but does'nt work on logon and logoff:

    create table log_trail (name varchar2(30), time date, action varchar2(10));

    create or replace trigger tr_logon
    after logon on database
    begin
    insert into log_trail values (user, sysdate, 'LOGON');
    commit;
    end tr_logon;

    create or replace trigger tr_logoff
    before logoff on database
    begin
    insert into log_trail values (user, sysdate, 'LOGOFF');
    commit;
    end tr_logon;

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    it works for me!

    Code:
    SQL> create table log_trail (name varchar2(30), time date, action varchar2(10));
    
    Table created.
    
    SQL> create or replace trigger tr_logon
    after logon on database
    begin
    insert into log_trail values (user, sysdate, 'LOGON');
    commit;
    end tr_logon;  2    3    4    5    6  
      7  /
    
    Trigger created.
    
    SQL> show error
    No errors.
    SQL> exit
    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    [oracle@localhost trace]$ sqlplus user1/user1
    
    SQL*Plus: Release 11.2.0.2.0 Production on Tue Apr 15 15:13:53 2014
    
    Copyright (c) 1982, 2010, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    SQL> exit     
    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    [oracle@localhost trace]$ sqlplus user1/user1
    
    SQL*Plus: Release 11.2.0.2.0 Production on Tue Apr 15 15:14:04 2014
    
    Copyright (c) 1982, 2010, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    SQL> select * from tr_logon;
    select * from tr_logon
                  *
    ERROR at line 1:
    ORA-00942: table or view does not exist
    
    
    SQL> select * from log_trail;
    
    NAME                           TIME      ACTION
    ------------------------------ --------- ----------
    SYS                            15-APR-14 LOGON
    SYS                            15-APR-14 LOGON
    USER1                          15-APR-14 LOGON
    USER1                          15-APR-14 LOGON
    
    SQL>
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    You don't have a schema on the table name in your trigger, did you make a public synonym to point to it. Did you grant insert to public on the table so anyone logging in can write to it. Do not grant select so no one but the owner or a dba account can read it.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Tags for this Thread

Posting Permissions

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