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.USER_TRIGGER_COLS

Source

View Text - Preformatted


select /*+ ORDERED NOCOST */ u.name, o.name, u2.name, o2.name,c.name,
   max(decode(tc.type#,0,'YES','NO')) COLUMN_LIST,
   decode(sum(decode(tc.type#,5,  1, -- one occurrence of new in
                              6,  2, -- one occurrence of old in
                              9,  4, -- one occurrence of new out
                             10,  8, -- one occurrence of old out (impossible)
                             13,  5, -- one occurrence of new in out
                             14, 10, -- one occurrence of old in out (imp.)
                             20, 16, -- one occurrence of parent in
                             24, 32, -- one occurrence of parent out (imp)
                             28, 64, -- one occurrence of parent in out (imp)
                              null)
                ), -- result in the following combinations across occurrences
           1, 'NEW IN',
           2, 'OLD IN',
           3, 'NEW IN OLD IN',
           4, 'NEW OUT',
           5, 'NEW IN OUT',
           6, 'NEW OUT OLD IN',
           7, 'NEW IN OUT OLD IN',
          16, 'PARENT IN',
          'NONE')
from sys.trigger$ t, sys."_CURRENT_EDITION_OBJ" o, sys.user$ u, sys.user$ u2,
     sys.col$ c, sys."_CURRENT_EDITION_OBJ" o2, sys.triggercol$ tc
where t.obj# = tc.obj#                -- find corresponding trigger definition
  and o.obj# = t.obj#                --    and corresponding trigger name
  and c.obj# = t.baseobject         -- and corresponding row in COL$ of
  and c.intcol# = tc.intcol#    -- the referenced column
  and bitand(c.property,32768) != 32768   -- not unused columns
  and bitand(o.flags, 128) = 0 -- not in recycle bin
  and o2.obj# = t.baseobject        -- and name of the table containing the trigger
  and u2.user# = o2.owner#        -- and name of the user who owns the table
  and u.user# = o.owner#        -- and name of user who owns the trigger
  and bitand(c.property,1) <> 1 -- and it is not an adt column
  and (bitand(t.property,32) <> 32 -- and it is not a nested table col
       or
      bitand(tc.type#,16) = 16) -- or it is a PARENT type column
    -- exclude TRIGGERCOL$ rows inserted to represent base table column
    -- for a DML trigger defined on a view
  and (bitand(tc.type#, 1024) = 0)
  and ((o.owner# = userenv('SCHEMAID') and u.user# = userenv('SCHEMAID')) -- triggers owned by the current user
      or
       (o2.owner# = userenv('SCHEMAID') and u2.user# = userenv('SCHEMAID'))) -- on the current user's tables
group by u.name, o.name, u2.name, o2.name,c.name
union all
select /*+ ORDERED NOCOST */ u.name, o.name, u2.name, o2.name,ac.name,
   max(decode(tc.type#,0,'YES','NO')) COLUMN_LIST,
   decode(sum(decode(tc.type#,5,  1, -- one occurrence of new in
                              6,  2, -- one occurrence of old in
                              9,  4, -- one occurrence of new out
                             10,  8, -- one occurrence of old out (impossible)
                             13,  5, -- one occurrence of new in out
                             14, 10, -- one occurrence of old in out (imp.)
                             20, 16, -- one occurrence of parent in
                             24, 32, -- one occurrence of parent out (imp)
                             28, 64, -- one occurrence of parent in out (imp)
                              null)
                ), -- result in the following combinations across occurrences
           1, 'NEW IN',
           2, 'OLD IN',
           3, 'NEW IN OLD IN',
           4, 'NEW OUT',
           5, 'NEW IN OUT',
           6, 'NEW OUT OLD IN',
           7, 'NEW IN OUT OLD IN',
          16, 'PARENT IN',
          'NONE')
from sys.trigger$ t, sys."_CURRENT_EDITION_OBJ" o, sys.user$ u, sys.user$ u2,
     sys.col$ c, sys.obj$ o2, sys.triggercol$ tc, sys.attrcol$ ac
where t.obj# = tc.obj#                -- find corresponding trigger definition
  and o.obj# = t.obj#                --    and corresponding trigger name
  and c.obj# = t.baseobject         -- and corresponding row in COL$ of
  and c.intcol# = tc.intcol#    -- the referenced column
  and bitand(c.property,32768) != 32768   -- not unused columns
  and bitand(o.flags, 128) = 0 -- not in recycle bin
  and o2.obj# = t.baseobject        -- and name of the table containing the trigger
  and u2.user# = o2.owner#        -- and name of the user who owns the table
  and u.user# = o.owner#        -- and name of user who owns the trigger
  and bitand(c.property,1) = 1  -- and it is an adt column
  and (bitand(t.property,32) <> 32 -- and it is not a nested table col
       or
      bitand(tc.type#,16) = 16) -- or it is a PARENT type column
  and ac.intcol# = c.intcol#
  and ac.obj# = c.obj#
  and ((o.owner# = userenv('SCHEMAID') and u.user# = userenv('SCHEMAID')) -- triggers owned by the current user
      or
       (o2.owner# = userenv('SCHEMAID') and u2.user# = userenv('SCHEMAID'))) -- on the current user's tables
group by u.name, o.name, u2.name, o2.name,ac.name
union all
select /*+ ORDERED NOCOST */ u.name, o.name, u2.name, o2.name, attr.name,
   max(decode(tc.type#,0,'YES','NO')) COLUMN_LIST,
   decode(sum(decode(tc.type#,5,  1, -- one occurrence of new in
                              6,  2, -- one occurrence of old in
                              9,  4, -- one occurrence of new out
                             10,  8, -- one occurrence of old out (impossible)
                             13,  5, -- one occurrence of new in out
                             14, 10, -- one occurrence of old in out (imp.)
                              null)
                ), -- result in the following combinations across occurrences
           1, 'NEW IN',
           2, 'OLD IN',
           3, 'NEW IN OLD IN',
           4, 'NEW OUT',
           5, 'NEW IN OUT',
           6, 'NEW OUT OLD IN',
           7, 'NEW IN OUT OLD IN',
          'NONE')
from sys.trigger$ t, sys."_CURRENT_EDITION_OBJ" o, sys.user$ u, sys.user$ u2,
     sys.obj$ o2, sys.triggercol$ tc,
     sys.collection$ coll, sys.coltype$ ctyp, sys.attribute$ attr
where t.obj# = tc.obj#                -- find corresponding trigger definition
  and o.obj# = t.obj#                --    and corresponding trigger name
  and o2.obj# = t.baseobject        -- and name of the table containing the trigger
  and u2.user# = o2.owner#        -- and name of the user who owns the table
  and u.user# = o.owner#        -- and name of user who owns the trigger
  and bitand(t.property,32) = 32 -- and it is not a nested table col
  and bitand(tc.type#,16) <> 16  -- and it is not a PARENT type column
  and bitand(o.flags, 128) = 0 -- not in recycle bin
  and ctyp.obj# = t.baseobject   -- find corresponding column type definition
  and ctyp.intcol# = t.nttrigcol -- and get the column type for the nested table
  and ctyp.toid = coll.toid      -- get the collection toid
  and ctyp.version# = coll.version# -- get the collection version
  and attr.attribute# = tc.intcol#  -- get the attribute number
  and attr.toid  = coll.elem_toid  -- get the attribute toid
  and attr.version# = coll.version#  -- get the attribute version
  and ((o.owner# = userenv('SCHEMAID') and u.user# = userenv('SCHEMAID')) -- triggers owned by the current user
      or
       (o2.owner# = userenv('SCHEMAID') and u2.user# = userenv('SCHEMAID'))) -- on the current user's tables
group by u.name, o.name, u2.name, o2.name,attr.name
union all
select /*+ ORDERED NOCOST */ u.name, o.name, u2.name, o2.name, 'COLUMN_VALUE',
   max(decode(tc.type#,0,'YES','NO')) COLUMN_LIST,
   decode(sum(decode(tc.type#,5,  1, -- one occurrence of new in
                              6,  2, -- one occurrence of old in
                              9,  4, -- one occurrence of new out
                             10,  8, -- one occurrence of old out (impossible)
                             13,  5, -- one occurrence of new in out
                             14, 10, -- one occurrence of old in out (imp.)
                              null)
                ), -- result in the following combinations across occurrences
           1, 'NEW IN',
           2, 'OLD IN',
           3, 'NEW IN OLD IN',
           4, 'NEW OUT',
           5, 'NEW IN OUT',
           6, 'NEW OUT OLD IN',
           7, 'NEW IN OUT OLD IN',
          'NONE')
from sys.trigger$ t, sys."_CURRENT_EDITION_OBJ" o, sys.user$ u, sys.user$ u2,
     sys.obj$ o2, sys.triggercol$ tc
where t.obj# = tc.obj#                -- find corresponding trigger definition
  and o.obj# = t.obj#                --    and corresponding trigger name
  and o2.obj# = t.baseobject        -- and name of the table containing the trigger
  and u2.user# = o2.owner#        -- and name of the user who owns the table
  and u.user# = o.owner#        -- and name of user who owns the trigger
  and bitand(t.property,32) = 32 -- and it is not a nested table col
  and bitand(tc.type#,16) <> 16  -- and it is not a PARENT type column
  and bitand(o.flags, 128) = 0 -- not in recycle bin
  and tc.intcol# = 0
  and ((o.owner# = userenv('SCHEMAID') and u.user# = userenv('SCHEMAID')) -- triggers owned by the current user
      or
       (o2.owner# = userenv('SCHEMAID') and u2.user# = userenv('SCHEMAID'))) -- on the current user's tables
group by u.name, o.name, u2.name, o2.name,'COLUMN_VALUE'

View Text - HTML Formatted

SELECT /*+ ORDERED NOCOST */ U.NAME
, O.NAME
, U2.NAME
, O2.NAME
, C.NAME
, MAX(DECODE(TC.TYPE#
, 0
, 'YES'
, 'NO')) COLUMN_LIST
, DECODE(SUM(DECODE(TC.TYPE#
, 5
, 1
, -- ONE OCCURRENCE OF NEW IN 6
, 2
, -- ONE OCCURRENCE OF OLD IN 9
, 4
, -- ONE OCCURRENCE OF NEW OUT 10
, 8
, -- ONE OCCURRENCE OF OLD OUT (IMPOSSIBLE) 13
, 5
, -- ONE OCCURRENCE OF NEW IN OUT 14
, 10
, -- ONE OCCURRENCE OF OLD IN OUT (IMP.) 20
, 16
, -- ONE OCCURRENCE OF PARENT IN 24
, 32
, -- ONE OCCURRENCE OF PARENT OUT (IMP) 28
, 64
, -- ONE OCCURRENCE OF PARENT IN OUT (IMP) NULL) )
, -- RESULT IN THE FOLLOWING COMBINATIONS ACROSS OCCURRENCES 1
, 'NEW IN'
, 2
, 'OLD IN'
, 3
, 'NEW IN OLD IN'
, 4
, 'NEW OUT'
, 5
, 'NEW IN OUT'
, 6
, 'NEW OUT OLD IN'
, 7
, 'NEW IN OUT OLD IN'
, 16
, 'PARENT IN'
, 'NONE') FROM SYS.TRIGGER$ T
, SYS."_CURRENT_EDITION_OBJ" O
, SYS.USER$ U
, SYS.USER$ U2
, SYS.COL$ C
, SYS."_CURRENT_EDITION_OBJ" O2
, SYS.TRIGGERCOL$ TC WHERE T.OBJ# = TC.OBJ# -- FIND CORRESPONDING TRIGGER DEFINITION
AND O.OBJ# = T.OBJ# --
AND CORRESPONDING TRIGGER NAME
AND C.OBJ# = T.BASEOBJECT --
AND CORRESPONDING ROW IN COL$ OF
AND C.INTCOL# = TC.INTCOL# -- THE REFERENCED COLUMN
AND BITAND(C.PROPERTY
, 32768) != 32768 -- NOT UNUSED COLUMNS
AND BITAND(O.FLAGS
, 128) = 0 -- NOT IN RECYCLE BIN
AND O2.OBJ# = T.BASEOBJECT --
AND NAME OF THE TABLE CONTAINING THE TRIGGER
AND U2.USER# = O2.OWNER# --
AND NAME OF THE USER WHO OWNS THE TABLE
AND U.USER# = O.OWNER# --
AND NAME OF USER WHO OWNS THE TRIGGER
AND BITAND(C.PROPERTY
, 1) <> 1 --
AND IT IS NOT AN ADT COLUMN
AND (BITAND(T.PROPERTY
, 32) <> 32 --
AND IT IS NOT A NESTED TABLE COL OR BITAND(TC.TYPE#
, 16) = 16) -- OR IT IS A PARENT TYPE COLUMN -- EXCLUDE TRIGGERCOL$ ROWS INSERTED TO REPRESENT BASE TABLE COLUMN -- FOR A DML TRIGGER DEFINED ON A VIEW
AND (BITAND(TC.TYPE#
, 1024) = 0)
AND ((O.OWNER# = USERENV('SCHEMAID')
AND U.USER# = USERENV('SCHEMAID')) -- TRIGGERS OWNED BY THE CURRENT USER OR (O2.OWNER# = USERENV('SCHEMAID')
AND U2.USER# = USERENV('SCHEMAID'))) -- ON THE CURRENT USER'S TABLES GROUP BY U.NAME
, O.NAME
, U2.NAME
, O2.NAME
, C.NAME UNION ALL SELECT /*+ ORDERED NOCOST */ U.NAME
, O.NAME
, U2.NAME
, O2.NAME
, AC.NAME
, MAX(DECODE(TC.TYPE#
, 0
, 'YES'
, 'NO')) COLUMN_LIST
, DECODE(SUM(DECODE(TC.TYPE#
, 5
, 1
, -- ONE OCCURRENCE OF NEW IN 6
, 2
, -- ONE OCCURRENCE OF OLD IN 9
, 4
, -- ONE OCCURRENCE OF NEW OUT 10
, 8
, -- ONE OCCURRENCE OF OLD OUT (IMPOSSIBLE) 13
, 5
, -- ONE OCCURRENCE OF NEW IN OUT 14
, 10
, -- ONE OCCURRENCE OF OLD IN OUT (IMP.) 20
, 16
, -- ONE OCCURRENCE OF PARENT IN 24
, 32
, -- ONE OCCURRENCE OF PARENT OUT (IMP) 28
, 64
, -- ONE OCCURRENCE OF PARENT IN OUT (IMP) NULL) )
, -- RESULT IN THE FOLLOWING COMBINATIONS ACROSS OCCURRENCES 1
, 'NEW IN'
, 2
, 'OLD IN'
, 3
, 'NEW IN OLD IN'
, 4
, 'NEW OUT'
, 5
, 'NEW IN OUT'
, 6
, 'NEW OUT OLD IN'
, 7
, 'NEW IN OUT OLD IN'
, 16
, 'PARENT IN'
, 'NONE') FROM SYS.TRIGGER$ T
, SYS."_CURRENT_EDITION_OBJ" O
, SYS.USER$ U
, SYS.USER$ U2
, SYS.COL$ C
, SYS.OBJ$ O2
, SYS.TRIGGERCOL$ TC
, SYS.ATTRCOL$ AC WHERE T.OBJ# = TC.OBJ# -- FIND CORRESPONDING TRIGGER DEFINITION
AND O.OBJ# = T.OBJ# --
AND CORRESPONDING TRIGGER NAME
AND C.OBJ# = T.BASEOBJECT --
AND CORRESPONDING ROW IN COL$ OF
AND C.INTCOL# = TC.INTCOL# -- THE REFERENCED COLUMN
AND BITAND(C.PROPERTY
, 32768) != 32768 -- NOT UNUSED COLUMNS
AND BITAND(O.FLAGS
, 128) = 0 -- NOT IN RECYCLE BIN
AND O2.OBJ# = T.BASEOBJECT --
AND NAME OF THE TABLE CONTAINING THE TRIGGER
AND U2.USER# = O2.OWNER# --
AND NAME OF THE USER WHO OWNS THE TABLE
AND U.USER# = O.OWNER# --
AND NAME OF USER WHO OWNS THE TRIGGER
AND BITAND(C.PROPERTY
, 1) = 1 --
AND IT IS AN ADT COLUMN
AND (BITAND(T.PROPERTY
, 32) <> 32 --
AND IT IS NOT A NESTED TABLE COL OR BITAND(TC.TYPE#
, 16) = 16) -- OR IT IS A PARENT TYPE COLUMN
AND AC.INTCOL# = C.INTCOL#
AND AC.OBJ# = C.OBJ#
AND ((O.OWNER# = USERENV('SCHEMAID')
AND U.USER# = USERENV('SCHEMAID')) -- TRIGGERS OWNED BY THE CURRENT USER OR (O2.OWNER# = USERENV('SCHEMAID')
AND U2.USER# = USERENV('SCHEMAID'))) -- ON THE CURRENT USER'S TABLES GROUP BY U.NAME
, O.NAME
, U2.NAME
, O2.NAME
, AC.NAME UNION ALL SELECT /*+ ORDERED NOCOST */ U.NAME
, O.NAME
, U2.NAME
, O2.NAME
, ATTR.NAME
, MAX(DECODE(TC.TYPE#
, 0
, 'YES'
, 'NO')) COLUMN_LIST
, DECODE(SUM(DECODE(TC.TYPE#
, 5
, 1
, -- ONE OCCURRENCE OF NEW IN 6
, 2
, -- ONE OCCURRENCE OF OLD IN 9
, 4
, -- ONE OCCURRENCE OF NEW OUT 10
, 8
, -- ONE OCCURRENCE OF OLD OUT (IMPOSSIBLE) 13
, 5
, -- ONE OCCURRENCE OF NEW IN OUT 14
, 10
, -- ONE OCCURRENCE OF OLD IN OUT (IMP.) NULL) )
, -- RESULT IN THE FOLLOWING COMBINATIONS ACROSS OCCURRENCES 1
, 'NEW IN'
, 2
, 'OLD IN'
, 3
, 'NEW IN OLD IN'
, 4
, 'NEW OUT'
, 5
, 'NEW IN OUT'
, 6
, 'NEW OUT OLD IN'
, 7
, 'NEW IN OUT OLD IN'
, 'NONE') FROM SYS.TRIGGER$ T
, SYS."_CURRENT_EDITION_OBJ" O
, SYS.USER$ U
, SYS.USER$ U2
, SYS.OBJ$ O2
, SYS.TRIGGERCOL$ TC
, SYS.COLLECTION$ COLL
, SYS.COLTYPE$ CTYP
, SYS.ATTRIBUTE$ ATTR WHERE T.OBJ# = TC.OBJ# -- FIND CORRESPONDING TRIGGER DEFINITION
AND O.OBJ# = T.OBJ# --
AND CORRESPONDING TRIGGER NAME
AND O2.OBJ# = T.BASEOBJECT --
AND NAME OF THE TABLE CONTAINING THE TRIGGER
AND U2.USER# = O2.OWNER# --
AND NAME OF THE USER WHO OWNS THE TABLE
AND U.USER# = O.OWNER# --
AND NAME OF USER WHO OWNS THE TRIGGER
AND BITAND(T.PROPERTY
, 32) = 32 --
AND IT IS NOT A NESTED TABLE COL
AND BITAND(TC.TYPE#
, 16) <> 16 --
AND IT IS NOT A PARENT TYPE COLUMN
AND BITAND(O.FLAGS
, 128) = 0 -- NOT IN RECYCLE BIN
AND CTYP.OBJ# = T.BASEOBJECT -- FIND CORRESPONDING COLUMN TYPE DEFINITION
AND CTYP.INTCOL# = T.NTTRIGCOL --
AND GET THE COLUMN TYPE FOR THE NESTED TABLE
AND CTYP.TOID = COLL.TOID -- GET THE COLLECTION TOID
AND CTYP.VERSION# = COLL.VERSION# -- GET THE COLLECTION VERSION
AND ATTR.ATTRIBUTE# = TC.INTCOL# -- GET THE ATTRIBUTE NUMBER
AND ATTR.TOID = COLL.ELEM_TOID -- GET THE ATTRIBUTE TOID
AND ATTR.VERSION# = COLL.VERSION# -- GET THE ATTRIBUTE VERSION
AND ((O.OWNER# = USERENV('SCHEMAID')
AND U.USER# = USERENV('SCHEMAID')) -- TRIGGERS OWNED BY THE CURRENT USER OR (O2.OWNER# = USERENV('SCHEMAID')
AND U2.USER# = USERENV('SCHEMAID'))) -- ON THE CURRENT USER'S TABLES GROUP BY U.NAME
, O.NAME
, U2.NAME
, O2.NAME
, ATTR.NAME UNION ALL SELECT /*+ ORDERED NOCOST */ U.NAME
, O.NAME
, U2.NAME
, O2.NAME
, 'COLUMN_VALUE'
, MAX(DECODE(TC.TYPE#
, 0
, 'YES'
, 'NO')) COLUMN_LIST
, DECODE(SUM(DECODE(TC.TYPE#
, 5
, 1
, -- ONE OCCURRENCE OF NEW IN 6
, 2
, -- ONE OCCURRENCE OF OLD IN 9
, 4
, -- ONE OCCURRENCE OF NEW OUT 10
, 8
, -- ONE OCCURRENCE OF OLD OUT (IMPOSSIBLE) 13
, 5
, -- ONE OCCURRENCE OF NEW IN OUT 14
, 10
, -- ONE OCCURRENCE OF OLD IN OUT (IMP.) NULL) )
, -- RESULT IN THE FOLLOWING COMBINATIONS ACROSS OCCURRENCES 1
, 'NEW IN'
, 2
, 'OLD IN'
, 3
, 'NEW IN OLD IN'
, 4
, 'NEW OUT'
, 5
, 'NEW IN OUT'
, 6
, 'NEW OUT OLD IN'
, 7
, 'NEW IN OUT OLD IN'
, 'NONE') FROM SYS.TRIGGER$ T
, SYS."_CURRENT_EDITION_OBJ" O
, SYS.USER$ U
, SYS.USER$ U2
, SYS.OBJ$ O2
, SYS.TRIGGERCOL$ TC WHERE T.OBJ# = TC.OBJ# -- FIND CORRESPONDING TRIGGER DEFINITION
AND O.OBJ# = T.OBJ# --
AND CORRESPONDING TRIGGER NAME
AND O2.OBJ# = T.BASEOBJECT --
AND NAME OF THE TABLE CONTAINING THE TRIGGER
AND U2.USER# = O2.OWNER# --
AND NAME OF THE USER WHO OWNS THE TABLE
AND U.USER# = O.OWNER# --
AND NAME OF USER WHO OWNS THE TRIGGER
AND BITAND(T.PROPERTY
, 32) = 32 --
AND IT IS NOT A NESTED TABLE COL
AND BITAND(TC.TYPE#
, 16) <> 16 --
AND IT IS NOT A PARENT TYPE COLUMN
AND BITAND(O.FLAGS
, 128) = 0 -- NOT IN RECYCLE BIN
AND TC.INTCOL# = 0
AND ((O.OWNER# = USERENV('SCHEMAID')
AND U.USER# = USERENV('SCHEMAID')) -- TRIGGERS OWNED BY THE CURRENT USER OR (O2.OWNER# = USERENV('SCHEMAID')
AND U2.USER# = USERENV('SCHEMAID'))) -- ON THE CURRENT USER'S TABLES GROUP BY U.NAME
, O.NAME
, U2.NAME
, O2.NAME
, 'COLUMN_VALUE'