/* This is on Oracle 10gR2 on AIX. I have not checked it on other releases or platforms*/
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, add a trailing space or 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.
Wednesday, April 15, 2009
Subscribe to:
Post Comments (Atom)
Followers
4 comments:
that's insane! which version are you running on? did you reproduce on another version?
It looks like this has been around in some form for awhile, all these are on Solaris (an aside, as far as I remember, these databases were all fresh installs, not upgrades):
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
PL/SQL Release 8.1.7.4.0 - Production
CORE 8.1.7.0.0 Production
TNS for Solaris: Version 8.1.7.4.0 - Production
NLSRTL Version 3.4.1.0.0 - Production
SQL> SELECT distinct triggering_event||'.' from dba_triggers;
TRIGGERING_EVENT||'.'
------------------------------------------------------------------------------------------------------------------------------------
DELETE.
DROP .
INSERT OR DELETE.
INSERT OR UPDATE OR DELETE.
INSERT OR UPDATE.
INSERT.
SHUTDOWN .
STARTUP .
UPDATE OR DELETE.
UPDATE.
10 rows selected.
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for Solaris: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production
SQL> SELECT distinct triggering_event||'.' from dba_triggers;
TRIGGERING_EVENT||'.'
------------------------------------------------------------------------------------------------------------------------------------
DELETE.
DROP .
INSERT OR UPDATE OR DELETE.
INSERT OR UPDATE.
INSERT.
SHUTDOWN .
STARTUP .
UPDATE OR DELETE.
8 rows selected.
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Solaris: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> SELECT triggering_event||'.' from dba_triggers;
TRIGGERING_EVENT||'.'
--------------------------------------------------------------------------------
INSERT.
UPDATE OR DELETE.
DROP .
STARTUP .
SHUTDOWN .
Interesting. Oracle will never stop surprising you. At leasts its complexity keeps people's rate keeps going higher, which is good !!!
Confirmed that this is exactly the same on Oracle 11g (11.1.0.7) running on Linux x86-64 as well. Very strange.
Post a Comment