Forum Stats

  • 3,851,690 Users
  • 2,264,012 Discussions
  • 7,904,818 Comments

Discussions

Categories

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

3888
3888 Member Posts: 188
edited Sep 18, 2009 8:18PM in General Database Discussions
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>
Tagged:
user13124373
· Share on TwitterShare on Facebook

Answers

  • 181444
    181444 Member Posts: 4,022
    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 --
    user13124373
    · Share on TwitterShare on Facebook
  • OrionNet
    OrionNet Member Posts: 4,542 Gold Trophy
    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;
    /
    · Share on TwitterShare on Facebook
  • 179130
    179130 Member Posts: 31
    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...
    · Share on TwitterShare on Facebook
This discussion has been closed.