DBA_TRIGGERS.TRIGGERING_EVENT predicate
Date: Tue, 14 Apr 2009 12:33:57 -0600
Message-ID: <49E4D715.9090601_at_optimaldba.com>
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/
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Apr 14 2009 - 13:33:57 CDT