在查询触发器的视图时碰到了一个有趣的问题。
通过一个SQL检查DBA_TRIGGERS中存在哪个触发事件：
SQL> SELECT DISTINCT TRIGGERING_EVENT FROM DBA_TRIGGERS;
TRIGGERING_EVENT
------------------------------
UPDATE OR DELETE
DROP
ALTER OR RENAME
INSERT OR UPDATE OR DELETE
INSERT OR UPDATE
DELETE
UPDATE
TRUNCATE
CREATE
ALTER
CREATE OR ALTER
INSERT
STARTUP
DROP OR TRUNCATE
SHUTDOWN
已选择15行。
下面检查数据库中触发事件为SHUTDOWN的触发器有哪些：
SQL> SELECT OWNER, TRIGGER_NAME
2 FROM DBA_TRIGGERS
3 WHERE TRIGGERING_EVENT = 'SHUTDOWN';
未选定行
居然没有找到，可是刚才的查询明明看到有触发事件为SHUTDOWN的触发器。基本上只有两种可能，一个是在我发出第二个查询之前，有人删除了这种类型的触发器，不过由于这个数据库是我本机的数据库，因此不存在这个问题。那么只剩下一种可能，就是查询结果后面还跟了看不到的字符。
首先验证这种触发事件的触发器仍然存在：
SQL> SELECT OWNER, TRIGGER_NAME
2 FROM DBA_TRIGGERS
3 WHERE TRIGGERING_EVENT LIKE 'SHUT%';
OWNER TRIGGER_NAME
------------------------------ ----------------------------
SYS AURORA$SERVER$SHUTDOWN
SYS OLAPISHUTDOWNTRIGGER
触发器果然存在，莫非所有的触发事件后面都添加了不可见字符：
SQL> SELECT DISTINCT TRIGGERING_EVENT || '.'
2 FROM DBA_TRIGGERS;
TRIGGERING_EVENT||'.'
------------------------------------------------------
TRUNCATE .
INSERT OR UPDATE.
DELETE.
CREATE .
INSERT OR UPDATE OR DELETE.
SHUTDOWN .
UPDATE.
ALTER .
UPDATE OR DELETE.
DROP OR TRUNCATE .
STARTUP .
DROP .
ALTER OR RENAME .
INSERT.
CREATE OR ALTER .
已选择15行。
奇怪的现象出现了，并不是每种触发事件后面都跟随了空格，所有DML以及DML组合事件都不带末尾的空格，而其他类型都是带空格的，这又是什么原因呢。
SQL> SELECT TEXT FROM DBA_VIEWS
2 WHERE VIEW_NAME = 'DBA_TRIGGERS';
TEXT
-------------------------------------------------------------------------------
select trigusr.name, trigobj.name,
decode(t.type#, 0, 'BEFORE STATEMENT',
1, 'BEFORE EACH ROW',
2, 'AFTER STATEMENT',
3, 'AFTER EACH ROW',
4, 'INSTEAD OF',
'UNDEFINED'),
decode(t.insert$*100 + t.update$*10 + t.delete$,
100, 'INSERT',
010, 'UPDATE',
001, 'DELETE',
110, 'INSERT OR UPDATE',
101, 'INSERT OR DELETE',
011, 'UPDATE OR DELETE',
111, 'INSERT OR UPDATE OR DELETE', 'ERROR'),
tabusr.name,
decode(bitand(t.property, 1), 1, 'VIEW',
0, 'TABLE',
'UNDEFINED'),
tabobj.name, NULL,
'REFERENCING NEW AS '||t.refnewname||' OLD AS '||t.refoldname,
t.whenclause,decode(t.enabled, 0, 'DISABLED', 1, 'ENABLED', 'ERROR'),
t.definition,
decode(bitand(t.property, 2), 2, 'CALL',
'PL/SQL '),
t.action#
from sys.obj$ trigobj, sys.obj$ tabobj, sys.trigger$ t,
sys.user$ tabusr, sys.user$ trigusr
where (trigobj.obj# = t.obj# and
tabobj.obj# = t.baseobject and
tabobj.owner# = tabusr.user# and
trigobj.owner# = trigusr.user# and
bitand(t.property, 63) < 8 )
union all
select trigusr.name, trigobj.name,
decode(t.type#, 0, 'BEFORE EVENT',
2, 'AFTER EVENT',
'UNDEFINED'),
decode(bitand(t.sys_evts, 1), 1, 'STARTUP ') ||
decode(bitand(t.sys_evts, 2), 2,
decode(sign(bitand(t.sys_evts, 1)), 1, 'OR SHUTDOWN ',
'SHUTDOWN ')) ||
decode(bitand(t.sys_evts, 4), 4,
decode(sign(bitand(t.sys_evts, 3)), 1, 'OR ERROR ',
'ERROR ')) ||
decode(bitand(t.sys_evts, 8), 8,
decode(sign(bitand(t.sys_evts, 7)), 1, 'OR LOGON ',
'LOGON ')) ||
decode(bitand(t.sys_evts, 16), 16,
decode(sign(bitand(t.sys_evts, 15)), 1, 'OR LOGOFF ',
'LOGOFF ')) ||
decode(bitand(t.sys_evts, 262176), 32,
decode(sign(bitand(t.sys_evts, 31)), 1, 'OR CREATE ',
'CREATE ')) ||
decode(bitand(t.sys_evts, 262208), 64,
decode(sign(bitand(t.sys_evts, 63)), 1, 'OR ALTER ',
'ALTER ')) ||
decode(bitand(t.sys_evts, 262272), 128,
decode(sign(bitand(t.sys_evts, 127)), 1, 'OR DROP ',
'DROP ')) ||
decode (bitand(t.sys_evts, 262400), 256,
decode(sign(bitand(t.sys_evts, 255)), 1, 'OR ANALYZE ',
'ANALYZE ')) ||
decode (bitand(t.sys_evts, 262656), 512,
decode(sign(bitand(t.sys_evts, 511)), 1, 'OR COMMENT ',
'COMMENT ')) ||
decode (bitand(t.sys_evts, 263168), 1024,
decode(sign(bitand(t.sys_evts, 1023)), 1, 'OR GRANT ',
'GRANT ')) ||
decode (bitand(t.sys_evts, 264192), 2048,
decode(sign(bitand(t.sys_evts, 2047)), 1, 'OR REVOKE ',
'REVOKE ')) ||
decode (bitand(t.sys_evts, 266240), 4096,
decode(sign(bitand(t.sys_evts, 4095)), 1, 'OR TRUNCATE ',
'TRUNCATE ')) ||
decode (bitand(t.sys_evts, 270336), 8192,
decode(sign(bitand(t.sys_evts, 8191)), 1, 'OR RENAME ',
'RENAME ')) ||
decode (bitand(t.sys_evts, 278528), 16384,
decode(sign(bitand(t.sys_evts, 16383)), 1, 'OR ASSOCIATE STATISTICS ',
'ASSOCIATE STATISTICS ')) ||
decode (bitand(t.sys_evts, 294912), 32768,
decode(sign(bitand(t.sys_evts, 32767)), 1, 'OR AUDIT ',
'AUDIT ')) ||
decode (bitand(t.sys_evts, 327680), 65536,
decode(sign(bitand(t.sys_evts, 65535)), 1,
'OR DISASSOCIATE STATISTICS ', 'DISASSOCIATE STATISTICS ')) ||
decode (bitand(t.sys_evts, 393216), 131072,
decode(sign(bitand(t.sys_evts, 131071)), 1, 'OR NOAUDIT ',
'NOAUDIT ')) ||
decode (bitand(t.sys_evts, 262144), 262144,
decode(sign(bitand(t.sys_evts, 31)), 1, 'OR DDL ',
'DDL ')) ||
decode (bitand(t.sys_evts, 8388608), 8388608,
decode(sign(bitand(t.sys_evts, 8388607)), 1, 'OR SUSPEND ',
'SUSPEND ')),
'SYS',
'DATABASE ',
NULL,
NULL,
'REFERENCING NEW AS '||t.refnewname||' OLD AS '||t.refoldname
|| decode(bitand(t.property,32),32,' PARENT AS ' || t.refprtname,NULL),
t.whenclause,decode(t.enabled, 0, 'DISABLED', 1, 'ENABLED', 'ERROR'),
t.definition,
decode(bitand(t.property, 2), 2, 'CALL',
'PL/SQL '),
t.action#
from sys.obj$ trigobj, sys.trigger$ t, sys.user$ trigusr
where (trigobj.obj# = t.obj# and
trigobj.owner# = trigusr.user# and
bitand(t.property, 63) >= 8 and bitand(t.property, 63) < 16)
union all
select trigusr.name, trigobj.name,
decode(t.type#, 0, 'BEFORE EVENT',
2, 'AFTER EVENT',
'UNDEFINED'),
decode(bitand(t.sys_evts, 1), 1, 'STARTUP ') ||
decode(bitand(t.sys_evts, 2), 2,
decode(sign(bitand(t.sys_evts, 1)), 1, 'OR SHUTDOWN ',
'SHUTDOWN ')) ||
decode(bitand(t.sys_evts, 4), 4,
decode(sign(bitand(t.sys_evts, 3)), 1, 'OR ERROR ',
'ERROR ')) ||
decode(bitand(t.sys_evts, 8), 8,
decode(sign(bitand(t.sys_evts, 7)), 1, 'OR LOGON ',
'LOGON ')) ||
decode(bitand(t.sys_evts, 16), 16,
decode(sign(bitand(t.sys_evts, 15)), 1, 'OR LOGOFF ',
'LOGOFF ')) ||
decode(bitand(t.sys_evts, 262176), 32,
decode(sign(bitand(t.sys_evts, 31)), 1, 'OR CREATE ',
'CREATE ')) ||
decode(bitand(t.sys_evts, 262208), 64,
decode(sign(bitand(t.sys_evts, 63)), 1, 'OR ALTER ',
'ALTER ')) ||
decode(bitand(t.sys_evts, 262272), 128,
decode(sign(bitand(t.sys_evts, 127)), 1, 'OR DROP ',
'DROP ')) ||
decode (bitand(t.sys_evts, 262400), 256,
decode(sign(bitand(t.sys_evts, 255)), 1, 'OR ANALYZE ',
'ANALYZE ')) ||
decode (bitand(t.sys_evts, 262656), 512,
decode(sign(bitand(t.sys_evts, 511)), 1, 'OR COMMENT ',
'COMMENT ')) ||
decode (bitand(t.sys_evts, 263168), 1024,
decode(sign(bitand(t.sys_evts, 1023)), 1, 'OR GRANT ',
'GRANT ')) ||
decode (bitand(t.sys_evts, 264192), 2048,
decode(sign(bitand(t.sys_evts, 2047)), 1, 'OR REVOKE ',
'REVOKE ')) ||
decode (bitand(t.sys_evts, 266240), 4096,
decode(sign(bitand(t.sys_evts, 4095)), 1, 'OR TRUNCATE ',
'TRUNCATE ')) ||
decode (bitand(t.sys_evts, 270336), 8192,
decode(sign(bitand(t.sys_evts, 8191)), 1, 'OR RENAME ',
'RENAME ')) ||
decode (bitand(t.sys_evts, 278528), 16384,
decode(sign(bitand(t.sys_evts, 16383)), 1, 'OR ASSOCIATE STATISTICS ',
'ASSOCIATE STATISTICS ')) ||
decode (bitand(t.sys_evts, 294912), 32768,
decode(sign(bitand(t.sys_evts, 32767)), 1, 'OR AUDIT ',
'AUDIT ')) ||
decode (bitand(t.sys_evts, 327680), 65536,
decode(sign(bitand(t.sys_evts, 65535)), 1,
'OR DISASSOCIATE STATISTICS ', 'DISASSOCIATE STATISTICS ')) ||
decode (bitand(t.sys_evts, 393216), 131072,
decode(sign(bitand(t.sys_evts, 131071)), 1, 'OR NOAUDIT ',
'NOAUDIT ')) ||
decode (bitand(t.sys_evts, 262144), 262144,
decode(sign(bitand(t.sys_evts, 31)), 1, 'OR DDL ',
'DDL ')) ||
decode (bitand(t.sys_evts, 8388608), 8388608,
decode(sign(bitand(t.sys_evts, 8388607)), 1, 'OR SUSPEND ',
'SUSPEND ')),
tabusr.name,
'SCHEMA',
NULL,
NULL,
'REFERENCING NEW AS '||t.refnewname||' OLD AS '||t.refoldname,
t.whenclause,decode(t.enabled, 0, 'DISABLED', 1, 'ENABLED', 'ERROR'),
t.definition,
decode(bitand(t.property, 2), 2, 'CALL',
'PL/SQL '),
t.action#
from sys.obj$ trigobj, sys.trigger$ t, sys.user$ tabusr, sys.user$ trigusr
where (trigobj.obj# = t.obj# and
trigobj.owner# = trigusr.user# and
bitand(t.property, 63) >= 16 and bitand(t.property, 63) < 32 and
tabusr.user# = t.baseobject)
union all
select trigusr.name, trigobj.name,
decode(t.type#, 0, 'BEFORE STATEMENT',
1, 'BEFORE EACH ROW',
2, 'AFTER STATEMENT',
3, 'AFTER EACH ROW',
4, 'INSTEAD OF',
'UNDEFINED'),
decode(t.insert$*100 + t.update$*10 + t.delete$,
100, 'INSERT',
010, 'UPDATE',
001, 'DELETE',
110, 'INSERT OR UPDATE',
101, 'INSERT OR DELETE',
011, 'UPDATE OR DELETE',
111, 'INSERT OR UPDATE OR DELETE', 'ERROR'),
tabusr.name,
decode(bitand(t.property, 1), 1, 'VIEW',
0, 'TABLE',
'UNDEFINED'),
tabobj.name, ntcol.name,
'REFERENCING NEW AS '||t.refnewname||' OLD AS '||t.refoldname ||
' PARENT AS ' || t.refprtname,
t.whenclause,decode(t.enabled, 0, 'DISABLED', 1, 'ENABLED', 'ERROR'),
t.definition,
decode(bitand(t.property, 2), 2, 'CALL',
'PL/SQL '),
t.action#
from sys.obj$ trigobj, sys.obj$ tabobj, sys.trigger$ t,
sys.user$ tabusr, sys.user$ trigusr, sys.viewtrcol$ ntcol
where (trigobj.obj# = t.obj# and
tabobj.obj# = t.baseobject and
tabobj.owner# = tabusr.user# and
trigobj.owner# = trigusr.user# and
t.nttrigcol = ntcol.intcol# and
t.nttrigatt = ntcol.attribute# and
t.baseobject = ntcol.obj# and
bitand(t.property, 63) >= 32)
其实只要看一下DBA_TRIGGERS视图的SQL就清楚了。
观察上面红色部分，由于触发器事件可以由多个事件组合，Oracle在存放的时候将不同事件转化为不同的数位存储，这样通过一个字段就可以表示多个事件的组合信息。而在视图显示的时候，通过DECODE判断数位，对于第一个事件显示事件名称和空格，以后出现的事件直接在后面追加OR和事件名称及空格。Oracle这里添加空格是为了方便处理多个事件组合的情况。
而对于DML的情况，由于一共可能产生的组合只有7种，Oracle将所有可能依次列出，因此就没有添加空格，代码参考上面蓝色部分。
看来不起眼的一个空格，Oracle在处理上也是经过思考的，不过个人认为，如果在红色代码的外面嵌套一层RTRIM，那么就真的完美了。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/4227/viewspace-234425/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/4227/viewspace-234425/
扫一扫
1509
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
