Results 1 to 5 of 5

Thread: logon trigger

  1. #1
    Join Date
    May 2007
    Posts
    5

    Unanswered: logon trigger

    I'm getting the following error when creating a trigger

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    6/1 PL/SQL: Statement ignored
    6/9 PLS-00405: subquery not allowed in this context

    this is the code of the trigger:

    create or replace trigger
    tr_logon_xv
    AFTER LOGON ON DATABASE
    BEGIN
    if ora_login_user<> 'USU_XV'
    then
    null;
    else
    if 1 in (select 1 from system.cadastro where host = sys_context('USERENV','HOST'))
    then
    null;
    else
    insert into SYSTEM.CADASTRO
    values( sys_context('USERENV','HOST'), systimestamp);
    end if;
    end if;
    END;
    /

    Thanks

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    how is "sys_context('USERENV','HOST')" different from V$SESSION.MACHINE?

    What are the requirements in words, that the trigger needs to implement?

    Post Operating System (OS) name & version for DB server system.
    Post results of
    SELECT * from v$version
    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
    May 2007
    Posts
    5
    I need insert in a table all hosts who connect the database with the user USU_XV

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    modify the trigger similar to below
    Code:
    SQL> @CADASTRO.sql
    SQL> drop table CADASTRO
      2  /
    
    Table dropped.
    
    SQL> create table CADASTRO(machine varchar2(30),logon_time date)
      2  /
    
    Table created.
    
    SQL> insert into CADASTRO values('FOOBAR',sysdate);
    
    1 row created.
    
    SQL> insert into CADASTRO values('BARFOO',sysdate);
    
    1 row created.
    
    SQL> commit;
    
    Commit complete.
    
    SQL> DECLARE
      2  CNT NUMBER;
      3  BEGIN
      4  SELECT COUNT(*) INTO CNT FROM CADASTRO WHERE MACHINE = 'bcm-laptop';
      5  if cnt = 0
      6  then
      7  	insert into CADASTRO values('bcm-laptop',sysdate);
      8  	commit;
      9  end if;
     10  end;
     11  /
    
    PL/SQL procedure successfully completed.
    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.

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    or:
    PHP Code:
    CREATE OR REPLACE TRIGGER audit_logon_trigger
    AFTER LOGON ON DATABASE
    BEGIN

    IF
       
    SYS_CONTEXT('USERENV','SESSION_USER') = 'USU_XV'
    THEN

    -- insert data into an audit-table
    -- table must be created first
    INSERT INTO SYSTEM
    .CADASTRO VALUES(
    user,
    sys_context('USERENV','SESSIONID'),
    sys_context('USERENV','HOST'),
    sysdate);

    END IF;
    END;

    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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