eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

[Home] [Help]

VIEW: SYS.USER_TRIGGERS

Source

View Text - Preformatted


select trigobj.name,
decode(t.type#, 0, 'BEFORE STATEMENT',
                1, 'BEFORE EACH ROW',
                2, 'AFTER STATEMENT',
                3, 'AFTER EACH ROW',
                4, 'INSTEAD OF',
                5, 'COMPOUND' ,
                   'UNDEFINED'),
decode(decode(bitand(t.property, 524288), 524288, 1000, 0) + t.insert$*100 + t.update$*10 + t.delete$,
                 0100, 'INSERT',
                 0010, 'UPDATE',
                 0001, 'DELETE',
                 0110, 'INSERT OR UPDATE',
                 0101, 'INSERT OR DELETE',
                 0011, 'UPDATE OR DELETE',
                 0111, 'INSERT OR UPDATE OR DELETE',
                 1000, 'LOGICAL LOB UPDATE',
                 'ERROR'),
u.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#,
decode(bitand(t.property, 8192),
       8192, decode(bitand(t.property, 131072),
              131072, 'REVERSE', 'FORWARD'), 'NO'),
decode(bitand(t.property, 256),
             256, 'YES', 'NO'),
decode(bitand(t.property, 512),
             512, 'YES', 'NO'),
decode(bitand(t.property, 1024),
             1024, 'YES', 'NO'),
decode(bitand(t.property, 2048),
             2048, 'YES', 'NO'),
decode(bitand(t.property, 4096 ),
             4096, 'YES', 'NO'),
decode(bitand(t.property, 128),    /* FOO */
             128, 'YES', 'NO'),
decode(bitand(t.property, 262144), /* ASO */
             262144, 'YES', 'NO')
  from sys."_CURRENT_EDITION_OBJ" trigobj,
  sys."_CURRENT_EDITION_OBJ" tabobj,
     sys.trigger$ t, sys.user$ u
where   (trigobj.obj#   = t.obj# and
         tabobj.obj# = t.baseobject and
         trigobj.owner# = userenv('SCHEMAID')
         and tabobj.owner#  = u.user#
         and bitand(t.property, 63) < 8
         and bitand(trigobj.flags, 128) = 0)
union all
select 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 ')) ||
decode (bitand(t.sys_evts, 33554432), 33554432,
        decode(sign(bitand(t.sys_evts, 33554431)), 1, 'OR CLONE ',
                                                      'CLONE ')) ||
decode (bitand(t.sys_evts, 67108864), 67108864,
        decode(sign(bitand(t.sys_evts, 67108863)), 1, 'OR UNPLUG ',
                                                      'UNPLUG ')),
'SYS',
decode(bitand(t.property, 8), 8, 'DATABASE        ', 'PLUGGABLE DATABASE'),
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#,
  'NO',
  'NO',
  'NO',
  'NO',
  'NO',
  'NO',
  'NO',
  'NO'
  from sys."_CURRENT_EDITION_OBJ" trigobj,
  sys.trigger$ t
where   (trigobj.obj#   = t.obj# and
         trigobj.owner# = userenv('SCHEMAID') and
         bitand(t.property, 48) = 0 and bitand(t.property, 1048584) != 0 and
         bitand(trigobj.flags, 128) = 0)
union all
select 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 ')),
u.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#,
'NO',
decode(bitand(t.property, 256),
             256, 'YES', 'NO'),
decode(bitand(t.property, 512),
             512, 'YES', 'NO'),
decode(bitand(t.property, 1024),
             1024, 'YES', 'NO'),
decode(bitand(t.property, 2048),
             2048, 'YES', 'NO'),
decode(bitand(t.property, 4096 ),
             4096, 'YES', 'NO'),
decode(bitand(t.property, 128),    /* FOO */
             128, 'YES', 'NO'),
decode(bitand(t.property, 262144), /* ASO */
             262144, 'YES', 'NO')
  from sys."_CURRENT_EDITION_OBJ" trigobj,
  sys.trigger$ t,
  sys.user$ u
where   (trigobj.obj#   = t.obj# and
         trigobj.owner# = userenv('SCHEMAID') and
         bitand(t.property, 63) >= 16 and bitand(t.property, 63) < 32 and
         u.user# = t.baseobject
         and bitand(trigobj.flags, 128) = 0)
union all
select trigobj.name,
decode(t.type#, 0, 'BEFORE STATEMENT',
               1, 'BEFORE EACH ROW',
               2, 'AFTER STATEMENT',
               3, 'AFTER EACH ROW',
               4, 'INSTEAD OF',
               5, 'COMPOUND',
               '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'),
u.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#,
decode(bitand(t.property, 8192),
       8192, decode(bitand(t.property, 131072),
                    131072, 'REVERSE', 'FORWARD'), 'NO'),
decode(bitand(t.property, 16384),
             16384, 'YES', 'NO'),
decode(bitand(t.property, 32768),
             32768, 'YES', 'NO'),
decode(bitand(t.property, 65536),
             65536, 'YES', 'NO'),
decode(bitand(t.property, 131072 ),
             131072, 'YES', 'NO'),
decode(bitand(t.property, 262144 ),
             262144, 'YES', 'NO'),
             'NO',
             'NO'
  from sys."_CURRENT_EDITION_OBJ" trigobj,
  sys."_CURRENT_EDITION_OBJ" tabobj,
  sys.trigger$ t,
  sys.user$ u,
  sys.viewtrcol$ ntcol
where   (trigobj.obj#   = t.obj# and
         tabobj.obj# = t.baseobject and
         trigobj.owner# = userenv('SCHEMAID')
         and bitand(trigobj.flags, 128) = 0
         and tabobj.owner#  = u.user#
         and bitand(t.property, 63) >= 32
         and t.nttrigcol = ntcol.intcol#
         and t.nttrigatt = ntcol.attribute#
         and t.baseobject = ntcol.obj#)

View Text - HTML Formatted

SELECT TRIGOBJ.NAME
, DECODE(T.TYPE#
, 0
, 'BEFORE STATEMENT'
, 1
, 'BEFORE EACH ROW'
, 2
, 'AFTER STATEMENT'
, 3
, 'AFTER EACH ROW'
, 4
, 'INSTEAD OF'
, 5
, 'COMPOUND'
, 'UNDEFINED')
, DECODE(DECODE(BITAND(T.PROPERTY
, 524288)
, 524288
, 1000
, 0) + T.INSERT$*100 + T.UPDATE$*10 + T.DELETE$
, 0100
, 'INSERT'
, 0010
, 'UPDATE'
, 0001
, 'DELETE'
, 0110
, 'INSERT OR UPDATE'
, 0101
, 'INSERT OR DELETE'
, 0011
, 'UPDATE OR DELETE'
, 0111
, 'INSERT OR UPDATE OR DELETE'
, 1000
, 'LOGICAL LOB UPDATE'
, 'ERROR')
, U.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#
, DECODE(BITAND(T.PROPERTY
, 8192)
, 8192
, DECODE(BITAND(T.PROPERTY
, 131072)
, 131072
, 'REVERSE'
, 'FORWARD')
, 'NO')
, DECODE(BITAND(T.PROPERTY
, 256)
, 256
, 'YES'
, 'NO')
, DECODE(BITAND(T.PROPERTY
, 512)
, 512
, 'YES'
, 'NO')
, DECODE(BITAND(T.PROPERTY
, 1024)
, 1024
, 'YES'
, 'NO')
, DECODE(BITAND(T.PROPERTY
, 2048)
, 2048
, 'YES'
, 'NO')
, DECODE(BITAND(T.PROPERTY
, 4096 )
, 4096
, 'YES'
, 'NO')
, DECODE(BITAND(T.PROPERTY
, 128)
, /* FOO */ 128
, 'YES'
, 'NO')
, DECODE(BITAND(T.PROPERTY
, 262144)
, /* ASO */ 262144
, 'YES'
, 'NO')
FROM SYS."_CURRENT_EDITION_OBJ" TRIGOBJ
, SYS."_CURRENT_EDITION_OBJ" TABOBJ
, SYS.TRIGGER$ T
, SYS.USER$ U WHERE (TRIGOBJ.OBJ# = T.OBJ# AND TABOBJ.OBJ# = T.BASEOBJECT AND TRIGOBJ.OWNER# = USERENV('SCHEMAID')
AND TABOBJ.OWNER# = U.USER#
AND BITAND(T.PROPERTY
, 63) < 8
AND BITAND(TRIGOBJ.FLAGS
, 128) = 0) UNION ALL SELECT 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 ')) || DECODE (BITAND(T.SYS_EVTS
, 33554432)
, 33554432
, DECODE(SIGN(BITAND(T.SYS_EVTS
, 33554431))
, 1
, 'OR CLONE '
, 'CLONE ')) || DECODE (BITAND(T.SYS_EVTS
, 67108864)
, 67108864
, DECODE(SIGN(BITAND(T.SYS_EVTS
, 67108863))
, 1
, 'OR UNPLUG '
, 'UNPLUG '))
, 'SYS'
, DECODE(BITAND(T.PROPERTY
, 8)
, 8
, 'DATABASE '
, 'PLUGGABLE DATABASE')
, 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#
, 'NO'
, 'NO'
, 'NO'
, 'NO'
, 'NO'
, 'NO'
, 'NO'
, 'NO'
FROM SYS."_CURRENT_EDITION_OBJ" TRIGOBJ
, SYS.TRIGGER$ T WHERE (TRIGOBJ.OBJ# = T.OBJ# AND TRIGOBJ.OWNER# = USERENV('SCHEMAID') AND BITAND(T.PROPERTY
, 48) = 0
AND BITAND(T.PROPERTY
, 1048584) != 0 AND BITAND(TRIGOBJ.FLAGS
, 128) = 0) UNION ALL SELECT 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 '))
, U.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#
, 'NO'
, DECODE(BITAND(T.PROPERTY
, 256)
, 256
, 'YES'
, 'NO')
, DECODE(BITAND(T.PROPERTY
, 512)
, 512
, 'YES'
, 'NO')
, DECODE(BITAND(T.PROPERTY
, 1024)
, 1024
, 'YES'
, 'NO')
, DECODE(BITAND(T.PROPERTY
, 2048)
, 2048
, 'YES'
, 'NO')
, DECODE(BITAND(T.PROPERTY
, 4096 )
, 4096
, 'YES'
, 'NO')
, DECODE(BITAND(T.PROPERTY
, 128)
, /* FOO */ 128
, 'YES'
, 'NO')
, DECODE(BITAND(T.PROPERTY
, 262144)
, /* ASO */ 262144
, 'YES'
, 'NO')
FROM SYS."_CURRENT_EDITION_OBJ" TRIGOBJ
, SYS.TRIGGER$ T
, SYS.USER$ U WHERE (TRIGOBJ.OBJ# = T.OBJ# AND TRIGOBJ.OWNER# = USERENV('SCHEMAID') AND BITAND(T.PROPERTY
, 63) >= 16
AND BITAND(T.PROPERTY
, 63) < 32 AND U.USER# = T.BASEOBJECT
AND BITAND(TRIGOBJ.FLAGS
, 128) = 0) UNION ALL SELECT TRIGOBJ.NAME
, DECODE(T.TYPE#
, 0
, 'BEFORE STATEMENT'
, 1
, 'BEFORE EACH ROW'
, 2
, 'AFTER STATEMENT'
, 3
, 'AFTER EACH ROW'
, 4
, 'INSTEAD OF'
, 5
, 'COMPOUND'
, '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')
, U.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#
, DECODE(BITAND(T.PROPERTY
, 8192)
, 8192
, DECODE(BITAND(T.PROPERTY
, 131072)
, 131072
, 'REVERSE'
, 'FORWARD')
, 'NO')
, DECODE(BITAND(T.PROPERTY
, 16384)
, 16384
, 'YES'
, 'NO')
, DECODE(BITAND(T.PROPERTY
, 32768)
, 32768
, 'YES'
, 'NO')
, DECODE(BITAND(T.PROPERTY
, 65536)
, 65536
, 'YES'
, 'NO')
, DECODE(BITAND(T.PROPERTY
, 131072 )
, 131072
, 'YES'
, 'NO')
, DECODE(BITAND(T.PROPERTY
, 262144 )
, 262144
, 'YES'
, 'NO')
, 'NO'
, 'NO'
FROM SYS."_CURRENT_EDITION_OBJ" TRIGOBJ
, SYS."_CURRENT_EDITION_OBJ" TABOBJ
, SYS.TRIGGER$ T
, SYS.USER$ U
, SYS.VIEWTRCOL$ NTCOL WHERE (TRIGOBJ.OBJ# = T.OBJ# AND TABOBJ.OBJ# = T.BASEOBJECT AND TRIGOBJ.OWNER# = USERENV('SCHEMAID')
AND BITAND(TRIGOBJ.FLAGS
, 128) = 0
AND TABOBJ.OWNER# = U.USER#
AND BITAND(T.PROPERTY
, 63) >= 32
AND T.NTTRIGCOL = NTCOL.INTCOL#
AND T.NTTRIGATT = NTCOL.ATTRIBUTE#
AND T.BASEOBJECT = NTCOL.OBJ#)