List triggers in MySQL database

Query below lists triggers in a database with their details.

Query

select trigger_schema as trigger_database,
       trigger_name,
       concat(event_object_schema, '.', event_object_table)  as trigger_table,
       action_order,
       action_timing,
       event_manipulation as trigger_event,
       action_statement as 'definition'
from information_schema.triggers 
where trigger_schema not in ('sys','mysql')
    -- and trigger_schema = 'database_name' -- put your database name here
order by trigger_schema,
         trigger_name;

Columns

  • trigger_database - name of the database in which trigger resides
  • trigger_name - name of the trigger
  • action_order - the ordinal position of the trigger's action within the list of triggers on the same table with the same trigger_event and action_timing
  • action_timing - trigger activation time:
    • before
    • after
  • trigger_event - specific SQL operation:
    • insert
    • update
    • delete
  • trigger_table - name of the trigger table with database (schema) name
  • definition - SQL definiton of trigger

Rows

  • One row represents one trigger
  • Scope of rows: all found triggers in a database
  • Ordered by trigger database name, trigger name

Sample results

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