Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

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

3888Dec 3 2008 — edited Sep 18 2009
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 ddsuser@dev10g
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 balatest@dev10g
Enter password: *********
Connected.
SQL>

Comments

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 --
1 person found this helpful
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...
1 - 3
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Oct 16 2009
Added on Dec 3 2008
#general-database-discussions, #trigger
3 comments
6,180 views