DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: trigger errors out

  1. #1
    Join Date
    Aug 2008
    Posts
    123

    trigger errors out

    Hi all,

    Would any one plase tell me what is wrong with this script:
    ALTER trigger logoff_audit_trigger
    BEFORE LOGOFF ON DATABASE
    BEGIN
    -- ***************************************************
    -- Update the last action accessed
    -- ***************************************************
    update 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 the last program accessed
    -- ***************************************************
    update 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 the last module accessed
    -- ***************************************************
    update 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 the logoff day
    -- ***************************************************
    update stats$user_log
    set logoff_day = sysdate
    where sys_context('USERENV','SESSIONID') = session_id;
    -- ***************************************************
    -- Update the logoff time
    -- ***************************************************
    update stats$user_log
    set logoff_time = to_char(sysdate, 'hh24:mi:ss')
    where sys_context('USERENV','SESSIONID') = session_id;
    -- ***************************************************
    -- Compute the elapsed minutes
    -- ***************************************************
    update stats$user_log
    set elapsed_minutes = round((logoff_day - logon_day)*1440)
    where sys_context('USERENV','SESSIONID') = session_id;
    COMMIT;
    END;
    /

    I still get error as followed:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    5/1 PL/SQL: SQL Statement ignored
    7/39 PL/SQL: ORA-00942: table or view does not exist
    12/1 PL/SQL: SQL Statement ignored
    13/41 PL/SQL: ORA-00942: table or view does not exist
    18/1 PL/SQL: SQL Statement ignored
    19/39 PL/SQL: ORA-00942: table or view does not exist

    the table is exist because other log on trigger works fine!!
    records from table:
    USER_ID SESSION_ID HOST LAST_PROGRAM LAST_ACTION LAST_MODULE LOGON_DAY LOGON_TIME LOGOFF_DA LOGOFF_TIM ELAPSED_MINUTES
    ------------------------------ ---------- ------------------------------ ------------------------------------------------ -------------------------------- -------------------------------- --------- ---------- --------- ---------- ---------------
    2032 1260 6434 14-AUG-08 09:07:10
    ADMIN 1261 6434 14-AUG-08 09:08:04
    2032 1262 6434 14-AUG-08 09:20:22

    3 rows selected.

    Thanks
    Pat

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    who owns the offending tables?
    why don't you fully qualify the table names?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    You need to do a direct grant on v$session to the schema that owns the trigger.
    By the way you could have made the trigger much cleaner.

    Code:
    ALTER trigger logoff_audit_trigger
    BEFORE LOGOFF ON DATABASE
       thisSession v$session%ROWTYPE;
    BEGIN
       SELECT *
         INTO thisSession
        WHERE sys_context('USERENV','SESSIONID') = audsid;
    
       UPDATE stats$user_log
          SET last_action     = thisSession.action,
              last_program    = thisSession.program,
              last_module     = thisSession.module,
              logoff_day      = SYSDATE,
              logoff_time     = TO_CHAR(SYSDATE, 'hh24:mi:ss'),
              elapsed_minutes = ROUND((SYSDATE - logon_day)*1440)
        WHERE sys_context('USERENV','SESSIONID') = session_id;
       COMMIT;
    END;
    /

  4. #4
    Join Date
    Aug 2008
    Posts
    123
    Hi,

    the table is belong to same user that created the trigger which is admin user!! but how can i find where it is located now because i can't seem to find the table or trigger in system but it is exist!!

    Please guide me!!

    thanks
    pat

  5. #5
    Join Date
    Aug 2008
    Posts
    123
    problem is the admin account i have is not sufficient to give priveliges to the schema owner where the table and triggere resides... how can i grant the permission without having sysDBA rights!! i can execute and select from V$session but when i try to run

    grant select on v$session to 2032
    *
    ERROR at line 1:
    ORA-01031: insufficient privileges

  6. #6
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Quote Originally Posted by midora
    how can i grant the permission without having sysDBA rights!!
    You can't! You might be able to get some of the information using context and not v$session.

    i.e. SYS_CONTEXT('USERENV','HOST')

    http://www.oracle-base.com/articles/...dAddresses.php

  7. #7
    Join Date
    Aug 2008
    Posts
    123
    also for the following trigger Can i script to exclude certain userid:::

    CREATE OR REPLACE TRIGGER LOGON_AUDIT_TRIGGER AFTER
    LOGON ON DATABASE BEGIN
    insert into tablename values(
    user,
    sys_context('USERENV','SESSIONID'),
    sys_context('USERENV','HOST'),
    null,
    null,
    null,
    sysdate,
    to_char(sysdate, 'hh24:mi:ss'),
    null,
    null,
    null
    );

    thanks
    Pat

  8. #8
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    So you are asking if you can add an if then else block to a trigger?

    Code:
    CREATE OR REPLACE TRIGGER LOGON_AUDIT_TRIGGER 
    AFTER LOGON ON DATABASE 
    BEGIN
       IF USER != 'RUMPLESTILTSKIN'
       THEN
          insert into tablename values(
             user,
             sys_context('USERENV','SESSIONID'),
             sys_context('USERENV','HOST'),
             null,         null,
             null,         sysdate,
             to_char(sysdate, 'hh24:mi:ss'),
             null,         null,   null );
       END IF;
    END;
    /

Posting Permissions

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


Click Here to Expand Forum to Full Width