Retrieve triggers available in the database

Triggers are the actions that are meant to be executed when a modification is done to the table contents. It can be defined as Insert, update or delete operation. This article is to check how we can retrieve triggers available in the database for all the tables. MySQL defines it as -

  • A trigger is a named database object that is associated with a table, and that activates when a particular event occurs for the table. Some uses for triggers are to perform checks of values to be inserted into a table or to perform calculations on values involved in an update.

Retrieve triggers from your database

Execute the following query to retrieve triggers defined in the tables of your database.

select event_object_schema as database_name,
       event_object_table as table_name,
       trigger_name,
       action_timing,
       event_manipulation as trigger_event,
       action_statement as 'definition'
from information_schema.triggers 
where event_object_schema = '<database_name>'

information_schema.triggers is the place where all such information is stored.

Column Name Value it contains
event_object_schema Database Name
event_object_table Table Name
trigger_name Name of the trigger defined in the table
action_timing Whether it should be Before or After making changes in the table.
event_manipulation Event Actions eg insert, update or delete.
action_statement What changes need to be made to the other tables after an action is triggered. Defined by a query statement.

Retrieve triggers that result in modification of a specific table.

For eg. If you notice there are too many modifications happening on a particular table, this is most likely to be caused by a trigger placed in one or more tables in your database.

To find a list of tables that are possibly triggering changes to a specific table, just add a simple condition in the above query -

select event_object_schema as database_name,
       event_object_table as table_name,
       trigger_name,
       action_timing,
       event_manipulation as trigger_event,
       action_statement as 'definition'
from information_schema.triggers 
where action_statement LIKE '%<table_name>%'
AND event_object_schema = '<database_name>'

This returns a list of tables having triggers defined in it for insert, update or delete operation that make changes in your table more often.