Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

So I'm trying to create my own audit table for some user management analysis and stumbled across a little problem. I use this trigger

CREATE OR REPLACE 
TRIGGER usr_t_audit_user_alnd
    AFTER LOGON ON DATABASE
    BEGIN
        INSERT INTO usr_t_audit_user (username, session_id, logon_day, logon_time)
        VALUES(UPPER(USER), SYS_CONTEXT('USERENV','SID'), SYSDATE, TO_CHAR(SYSDATE, 'HH24:MI:SS'));
    END;

to insert some standard informations into my table. It works fine, except the logoff time inside my other trigger isn't always that correct. But my actual problem is, that I don't want to insert all user logons. For example I only want to audit some chosen users. How can I achieve such a restriction? I can't put a WHERE-clause in my trigger. Anybody got some ideas?

share|improve this question
3  
Is there a reason that you aren't using Oracle's built-in audit functionality for this? Why can't you put a WHERE clause in your trigger (transforming the INSERT ... VALUES into an INSERT ... SELECT that may return 0 rows)? Or add an IF statement if you really need to roll your own auditing? –  Justin Cave Sep 30 '13 at 21:35

2 Answers 2

up vote 0 down vote accepted

You probably want to use something like,

CREATE OR REPLACE TRIGGER usr_t_audit_user_alnd AFTER LOGON ON DATABASE
BEGIN
  -- insert audit record only if user is one of specified, else ignore
  if ora_login_user in ('USER_A','USER_C','USER_D') then
    INSERT INTO usr_t_audit_user (username, session_id, logon_day, logon_time)
    VALUES(UPPER(USER), SYS_CONTEXT('USERENV','SID'), SYSDATE, TO_CHAR(SYSDATE, 'HH24:MI:SS'));
  end if;
END;
share|improve this answer

you can add IF condition around the INSERT stmt in your trigger.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.