Oracle LOGON trigger at Database Level is not firing???

3888

I have connected as DBA and created LOGON trigger. Disconnected as DBA and logged in as a Database user and did not raise any error. What am I missing?

Please see below...



SQL> conn [email protected]
Enter password: *********
Connected.
SQL> CREATE OR REPLACE TRIGGER KHAM_TEMP
2 AFTER LOGON ON DATABASE
3 --
4 BEGIN
5 RAISE_APPLICATION_ERROR(-20003,'Body');
6 exception
7 WHEN OTHERS THEN
8 RAISE_APPLICATION_ERROR(-20003,'Exception');
9 END;
10 /

Trigger created.

SQL> disconn
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn [email protected]
Enter password: *********
Connected.
SQL>

181444

First check the status in dba_triggers for the trigger to be sure it is "ENABLED"

Check to see if the user you tested with has the ADMINISTER DATABASE TRIGGER system privilege in which case I believe that the trigger does not fire for users with this privilege.

HTH -- Mark D Powell --

OrionNet

I have tested the same trigger on sys schema and it worked just fine.
Make sure you have right set of privs

CREATE OR REPLACE TRIGGER dts_trg
AFTER LOGON
ON DATABASE
DECLARE
BEGIN
INSERT INTO kham_temp.t (action)
VALUES ('user logged in');
EXCEPTION
WHEN OTHERS
THEN
-- Consider logging the error and then re-raise
RAISE;
END dts_trg;
/

179130

I got caught out with the following undocumented parameter that stopped my LOGON triggers firing:

systemtrig_enabled=FALSE

It was during a test upgrade, which failed 1/2 way through...