Introduction

Triggers in MySQL are stored procedures that execute automatically when a specific event (INSERT, UPDATE, or DELETE) occurs on a table. They are used for enforcing business rules, maintaining audit logs, and keeping related tables in sync. When managing a database, you need to know which triggers exist, what tables they are attached to, and what they do. This article covers the different ways to list all triggers in a MySQL database, with examples and explanations for each approach.

What Are MySQL Triggers?

A trigger is a named database object that is associated with a table and fires in response to a DML (Data Manipulation Language) event. Each trigger specifies three things: the timing (BEFORE or AFTER), the event (INSERT, UPDATE, or DELETE), and the action (the SQL statements to execute).

Here is a simple trigger that logs every insert into an orders table.

1CREATE TRIGGER orders_after_insert
2AFTER INSERT ON orders
3FOR EACH ROW
4BEGIN
5    INSERT INTO order_audit (order_id, action, created_at)
6    VALUES (NEW.id, 'INSERT', NOW());
7END;

This trigger fires after each row is inserted into orders and records the event in an order_audit table.

Method 1: SHOW TRIGGERS Statement

The simplest way to list triggers is the SHOW TRIGGERS statement.

SHOW TRIGGERS;

This returns all triggers in the currently selected database. The result includes columns like Trigger, Event, Table, Statement, Timing, and Created.

To filter triggers for a specific table, use the FROM or IN clause combined with LIKE.

SHOW TRIGGERS LIKE 'orders';

This shows only triggers associated with the orders table.

You can also filter by database.

SHOW TRIGGERS FROM my_database;

The output is a table with these key columns.

Method 2: Querying INFORMATION_SCHEMA.TRIGGERS

For more control over the output, query the INFORMATION_SCHEMA.TRIGGERS table directly. This approach lets you filter, sort, and select specific columns.

1SELECT
2    TRIGGER_NAME,
3    EVENT_MANIPULATION,
4    EVENT_OBJECT_TABLE,
5    ACTION_TIMING,
6    ACTION_STATEMENT
7FROM
8    INFORMATION_SCHEMA.TRIGGERS
9WHERE
10    TRIGGER_SCHEMA = 'my_database'
11ORDER BY
12    EVENT_OBJECT_TABLE, ACTION_TIMING;

This query returns all triggers in my_database, sorted by table and timing. The INFORMATION_SCHEMA approach is particularly useful in scripts and automation because you can shape the output to match your needs.

Useful Columns in INFORMATION_SCHEMA.TRIGGERS

• TRIGGER_NAME: The name of the trigger.
• EVENT_MANIPULATION: The DML event that fires the trigger (INSERT, UPDATE, DELETE).
• EVENT_OBJECT_TABLE: The table the trigger is attached to.
• ACTION_STATEMENT: The SQL code that executes when the trigger fires.
• ACTION_TIMING: Whether the trigger fires BEFORE or AFTER the event.
• TRIGGER_SCHEMA: The database the trigger belongs to.

Method 3: Using mysqldump

If you need to export trigger definitions along with your schema, mysqldump includes triggers by default.

mysqldump --triggers --no-data -u root -p my_database

This outputs the CREATE TRIGGER statements for all triggers in the database, which is useful for version control and migration scripts.

To exclude triggers from a dump, use --skip-triggers.

mysqldump --skip-triggers --no-data -u root -p my_database

Listing Triggers for a Specific Table

When you need to inspect triggers on a single table, combine INFORMATION_SCHEMA with a WHERE clause.

1SELECT
2    TRIGGER_NAME,
3    ACTION_TIMING,
4    EVENT_MANIPULATION,
5    ACTION_STATEMENT
6FROM
7    INFORMATION_SCHEMA.TRIGGERS
8WHERE
9    TRIGGER_SCHEMA = 'my_database'
10    AND EVENT_OBJECT_TABLE = 'orders';

This is more precise than SHOW TRIGGERS LIKE 'orders' because the LIKE clause in SHOW TRIGGERS matches against the table name pattern, which can return unexpected results if table names share prefixes.

Practical Example: Auditing All Triggers

Here is a query that produces a clean summary of every trigger in a database, showing the trigger name, when it fires, and what it does.

1SELECT
2    TRIGGER_NAME AS 'Trigger',
3    CONCAT(ACTION_TIMING, ' ', EVENT_MANIPULATION) AS 'Fires On',
4    EVENT_OBJECT_TABLE AS 'Table',
5    LEFT(ACTION_STATEMENT, 100) AS 'Action (truncated)'
6FROM
7    INFORMATION_SCHEMA.TRIGGERS
8WHERE
9    TRIGGER_SCHEMA = DATABASE()
10ORDER BY
11    EVENT_OBJECT_TABLE, ACTION_TIMING, EVENT_MANIPULATION;

The DATABASE() function returns the currently selected database, so you do not need to hardcode the name. The LEFT() function truncates long trigger bodies for readability.

Common Pitfalls

1. Not selecting the right database. SHOW TRIGGERS only shows triggers in the current database. If you see an empty result, make sure you ran USE my_database first, or specify the database explicitly with FROM.
2. Insufficient privileges. You need the TRIGGER privilege on the table or the SUPER privilege to view trigger definitions. If the ACTION_STATEMENT column returns empty, check your user permissions.
3. Hidden trigger interactions. Multiple triggers on the same table can interact in unexpected ways. If you have both a BEFORE INSERT and AFTER INSERT trigger, they execute in that order for every row. List all triggers on a table before adding new ones to avoid conflicts.
4. Trigger-based performance issues. Triggers add overhead to every DML operation on their table. If a table has many triggers and handles high write volume, the cumulative cost can be significant. Use the queries above to audit which triggers exist and whether they are all still necessary.
5. Forgetting about trigger ordering. Starting with MySQL 5.7.2, you can have multiple triggers with the same timing and event on the same table, ordered with FOLLOWS and PRECEDES. The INFORMATION_SCHEMA query does not show this ordering by default. Check ACTION_ORDER if trigger execution order matters.

Summary

MySQL provides three main ways to list triggers: SHOW TRIGGERS for quick interactive checks, INFORMATION_SCHEMA.TRIGGERS for scriptable and filterable queries, and mysqldump --triggers for exporting definitions. Use the INFORMATION_SCHEMA approach when you need to filter by table, sort results, or integrate trigger inspection into automated tooling. Always verify your database context and user privileges when trigger queries return empty results.