DBA_TRIGGERS.TRIGGERING_EVENT predicate
- From: Daniel Fink <daniel.fink@xxxxxxxxxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 14 Apr 2009 12:33:57 -0600
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.
--
Daniel Fink
OptimalDBA.com - Oracle Performance, Diagnosis, Data Recovery and Training
OptimalDBA http://www.optimaldba.com
Oracle Blog http://optimaldba.blogspot.com
Lost Data? http://www.ora600.be/
--
//www.freelists.org/webpage/oracle-l
Other related posts:
