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.

VIEW: SYS.ALL_TRIGGERS

Source

View Text - Preformatted


select triguser.name, 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'),
tabuser.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$ tabuser,
  sys.user$ triguser
where (trigobj.obj#   = t.obj# and
       tabobj.obj#    = t.baseobject and
       trigobj.owner# = triguser.user# and
       tabobj.owner#  = tabuser.user# and
       bitand(trigobj.flags, 128) = 0 and
       bitand(t.property, 63)    < 8  and
       (
        trigobj.owner# = userenv('SCHEMAID') or
        tabobj.owner# = userenv('SCHEMAID') or
        tabobj.obj# in
          (select oa1.obj# from sys.objauth$ oa1 where grantee# in
             (select kzsrorol from x$kzsro)) or
        exists (select null from v$enabledprivs
                where priv_number = -152 /* CREATE ANY TRIGGER */)))
union all
select triguser.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 ')) ||
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,
  sys.user$ triguser
where (trigobj.obj#    = t.obj# and
       trigobj.owner#  = triguser.user# and
       bitand(t.property, 48) = 0 and bitand(t.property, 1048584) != 0 and
       bitand(trigobj.flags, 128) = 0 and
       (
        trigobj.owner# = userenv('SCHEMAID') or
        exists (select null from v$enabledprivs
                where priv_number = -152 /* CREATE ANY TRIGGER */)))
union all
select triguser.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 ')),
tabuser.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',
  'NO',
  'NO',
  'NO',
  'NO',
  'NO',
  'NO',
  'NO'
  from sys."_CURRENT_EDITION_OBJ" trigobj,
  sys.trigger$ t,
  sys.user$ tabuser,
  sys.user$ triguser
where (trigobj.obj#    = t.obj# and
       trigobj.owner#  = triguser.user# and
       tabuser.user#   = t.baseobject and
       bitand(t.property, 63)     >= 16 and bitand(t.property, 63) < 32 and
       bitand(trigobj.flags, 128) = 0 and
       (
         trigobj.owner# = userenv('SCHEMAID') or
        tabuser.user#  = userenv('SCHEMAID') or
        exists (select null from v$enabledprivs
                where priv_number = -152 /* CREATE ANY TRIGGER */)))
union all
select triguser.name, 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'),
tabuser.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, 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$ tabuser,
  sys.user$ triguser,
     sys.viewtrcol$ ntcol
where (trigobj.obj#   = t.obj# and
       tabobj.obj#    = t.baseobject and
       trigobj.owner# = triguser.user# and
       tabobj.owner#  = tabuser.user# and
       bitand(t.property, 63)    >=  32  and
       bitand(trigobj.flags, 128) = 0 and
       t.nttrigcol = ntcol.intcol# and
       t.nttrigatt = ntcol.attribute# and
       t.baseobject = ntcol.obj# and
       (
        trigobj.owner# = userenv('SCHEMAID') or
        tabobj.owner# = userenv('SCHEMAID') or
        tabobj.obj# in
          (select oa3.obj# from sys.objauth$ oa3 where grantee# in
             (select kzsrorol from x$kzsro)) or
        exists (select null from v$enabledprivs
                where priv_number = -152 /* CREATE ANY TRIGGER */)))

View Text - HTML Formatted

SELECT TRIGUSER.NAME
, 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')
, TABUSER.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$ TABUSER
, SYS.USER$ TRIGUSER WHERE (TRIGOBJ.OBJ# = T.OBJ# AND TABOBJ.OBJ# = T.BASEOBJECT AND TRIGOBJ.OWNER# = TRIGUSER.USER# AND TABOBJ.OWNER# = TABUSER.USER# AND BITAND(TRIGOBJ.FLAGS
, 128) = 0 AND BITAND(T.PROPERTY
, 63) < 8 AND ( TRIGOBJ.OWNER# = USERENV('SCHEMAID') OR TABOBJ.OWNER# = USERENV('SCHEMAID') OR TABOBJ.OBJ# IN (SELECT OA1.OBJ#
FROM SYS.OBJAUTH$ OA1
WHERE GRANTEE# IN (SELECT KZSROROL
FROM X$KZSRO)) OR EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER = -152 /* CREATE ANY TRIGGER */))) UNION ALL SELECT TRIGUSER.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 ')) || 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
, SYS.USER$ TRIGUSER WHERE (TRIGOBJ.OBJ# = T.OBJ# AND TRIGOBJ.OWNER# = TRIGUSER.USER# AND BITAND(T.PROPERTY
, 48) = 0
AND BITAND(T.PROPERTY
, 1048584) != 0 AND BITAND(TRIGOBJ.FLAGS
, 128) = 0 AND ( TRIGOBJ.OWNER# = USERENV('SCHEMAID') OR EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER = -152 /* CREATE ANY TRIGGER */))) UNION ALL SELECT TRIGUSER.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 '))
, TABUSER.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'
, 'NO'
, 'NO'
, 'NO'
, 'NO'
, 'NO'
, 'NO'
, 'NO'
FROM SYS."_CURRENT_EDITION_OBJ" TRIGOBJ
, SYS.TRIGGER$ T
, SYS.USER$ TABUSER
, SYS.USER$ TRIGUSER WHERE (TRIGOBJ.OBJ# = T.OBJ# AND TRIGOBJ.OWNER# = TRIGUSER.USER# AND TABUSER.USER# = T.BASEOBJECT AND BITAND(T.PROPERTY
, 63) >= 16
AND BITAND(T.PROPERTY
, 63) < 32 AND BITAND(TRIGOBJ.FLAGS
, 128) = 0 AND ( TRIGOBJ.OWNER# = USERENV('SCHEMAID') OR TABUSER.USER# = USERENV('SCHEMAID') OR EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER = -152 /* CREATE ANY TRIGGER */))) UNION ALL SELECT TRIGUSER.NAME
, 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')
, TABUSER.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
, 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$ TABUSER
, SYS.USER$ TRIGUSER
, SYS.VIEWTRCOL$ NTCOL WHERE (TRIGOBJ.OBJ# = T.OBJ# AND TABOBJ.OBJ# = T.BASEOBJECT AND TRIGOBJ.OWNER# = TRIGUSER.USER# AND TABOBJ.OWNER# = TABUSER.USER# AND BITAND(T.PROPERTY
, 63) >= 32 AND BITAND(TRIGOBJ.FLAGS
, 128) = 0 AND T.NTTRIGCOL = NTCOL.INTCOL# AND T.NTTRIGATT = NTCOL.ATTRIBUTE# AND T.BASEOBJECT = NTCOL.OBJ# AND ( TRIGOBJ.OWNER# = USERENV('SCHEMAID') OR TABOBJ.OWNER# = USERENV('SCHEMAID') OR TABOBJ.OBJ# IN (SELECT OA3.OBJ#
FROM SYS.OBJAUTH$ OA3
WHERE GRANTEE# IN (SELECT KZSROROL
FROM X$KZSRO)) OR EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER = -152 /* CREATE ANY TRIGGER */)))