Re: DBA_TRIGGERS.TRIGGERING_EVENT predicate

From: Tim Gorman <tim_at_evdbt.com>
Date: Tue, 14 Apr 2009 21:13:39 -0600
Message-ID: <49E550E3.7040101_at_evdbt.com>

Oracle Support knows all about it too -- Metalink note #251044.1 (entitled "How to set a NLS session parameter at database or schema level for all connections?") explains how to query for AFTER LOGON triggers at the bottom of the note...

------------------------- begin excerpted text --------------------
Again, the client character set CANNOT be set or defined this way.

This select gives you all defined after logon triggers:

select OWNER, TRIGGER_NAME, TRIGGER_BODY from DBA_TRIGGERS where trim(TRIGGERING_EVENT) = 'LOGON';


Further information can be found in:

Note 241047.1 The Priority of NLS Parameters Explained.
Note 158577.1 NLS_LANG Explained (How does Client-Server Character Conversion Work?)
Note 179133.1 The correct NLS_LANG in a Windows Environment
------------------------- end excerpted text --------------------



Daniel Fink wrote:

In checking the logon triggers, I came across a little situation.

select trigger_name, trigger_body
from dba_triggers
where owner = 'DEMO'
and triggering_event = 'LOGON'

no rows selected

SELECT distinct triggering_event
from dba_triggers
where owner = 'DEMO'

TRIGGERING_EVENT
----------------------------------------
INSERT
LOGON
INSERT OR UPDATE OR DELETE
UPDATE OR DELETE
UPDATE
INSERT OR UPDATE

6 rows selected.

So...LOGON is certainly a valid event, so I decide to retype the query and rerun it...

select trigger_name, trigger_body
from dba_triggers
where owner = 'DEMO'
and triggering_event = 'LOGON'

no rows selected

After trying the query several times and expecting different results (yes...I know...the definition of insanity), I checked the actual data being returned using the DUMP function.


select distinct triggering_event, dump(triggering_event)
from dba_triggers

TRIGGERING_EVENT
----------------------------------------------------------------------------------------------------
DUMP(TRIGGERING_EVENT)
----------------------------------------------------------------------------------------------------
UPDATE
Typ=1 Len=6: 85,80,68,65,84,69

UPDATE OR DELETE
Typ=1 Len=16: 85,80,68,65,84,69,32,79,82,32,68,69,76,69,84,69

INSERT OR UPDATE OR DELETE
Typ=1 Len=26: 73,78,83,69,82,84,32,79,82,32,85,80,68,65,84,69,32,79,82,32,68,69,76,69,84,69

DELETE
Typ=1 Len=6: 68,69,76,69,84,69

DROP
Typ=1 Len=5: 68,82,79,80,32

LOGON
Typ=1 Len=6: 76,79,71,79,78,32

INSERT
Typ=1 Len=6: 73,78,83,69,82,84

INSERT OR UPDATE
Typ=1 Len=16: 73,78,83,69,82,84,32,79,82,32,85,80,68,65,84,69

It seems that the view includes a single space (ascii 32) to the end of some of the events. In looking at the view text, I see this when they concatenate triggering events together. Way to code a view!

So if you are using the TRIGGERING_EVENT as part of a predicate, wrap it in the TRIM() function or you may not get the results you want!

select trigger_name
from dba_triggers
where owner = 'DEMO'
and triggering_event = 'LOGON '

TRIGGER_NAME
------------------------------
KATHY_START_TRACE
TIM_START_TRACE
START_TRACE
DFINK_START_TRACE
CLOSE_START_TRACE

5 rows selected.

select trigger_name
from dba_triggers
where owner = 'DEMO'
and TRIM(triggering_event) = 'LOGON'

TRIGGER_NAME
------------------------------
KATHY_START_TRACE
TIM_START_TRACE
START_TRACE
DFINK_START_TRACE
CLOSE_START_TRACE

5 rows selected.

-- http://www.freelists.org/webpage/oracle-l Received on Tue Apr 14 2009 - 22:13:39 CDT