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?
audit
functionality for this? Why can't you put aWHERE
clause in your trigger (transforming theINSERT ... VALUES
into anINSERT ... SELECT
that may return 0 rows)? Or add anIF
statement if you really need to roll your own auditing?