List triggers by table name in Db2 database

Article for: Teradata MySQL MariaDB

Query below lists tables with their triggers.

Query

select 
    tabschema concat '.' concat tabname as table_name, 
    trigname as trigger_name,
    case trigtime 
         when 'B' then 'before'
         when 'A' then 'after'
         when 'I' then 'instead of' 
    end as activation,
    rtrim(case when eventupdate ='Y' then  'update ' else '' end 
          concat 
          case when eventdelete ='Y' then  'delete ' else '' end
          concat
          case when eventinsert ='Y' then  'insert ' else '' end)
    as event,
    case when ENABLED = 'N' then 'disabled'
    else 'active' end as status,
    text as definition
from syscat.triggers t
where tabschema not like 'SYS%'
order by table_name, trigger_name

Columns

  • table_name - name of the trigger table (for table triggers) with schema name
  • trigger_name - name of the trigger
  • activation - trigger activation time: before, after or instead of
  • event - specific SQL operation: insert, update or delete
  • status - trigger status
    • Active
    • Disabled
  • definition - SQL definiton of trigger

Rows

  • One row represents one trigger
  • Scope of rows: all column, table triggers in a database
  • Ordered by schema and table name, trigger name

Sample results

0
There are no comments. Click here to write the first comment.