How to Show Triggers in MySQL: Syntax and Examples

To show triggers in MySQL, use the SHOW TRIGGERS command which lists all triggers in the current database. You can also query the information_schema.TRIGGERS table for detailed trigger information.

Syntax

The basic command to list triggers in MySQL is SHOW TRIGGERS;. This shows all triggers in the current database. Alternatively, you can query the information_schema.TRIGGERS table to get detailed information about triggers, including their event, timing, and definition.

sql

SHOW TRIGGERS;

-- Or to get detailed info:
SELECT TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE, ACTION_TIMING, ACTION_STATEMENT
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = DATABASE();

Example

This example shows how to create a simple trigger and then list all triggers in the current database using SHOW TRIGGERS. It demonstrates the trigger's presence and details.

sql

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  salary INT
);

DELIMITER $$
CREATE TRIGGER before_employee_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
  SET NEW.salary = IFNULL(NEW.salary, 0);
END$$
DELIMITER ;

SHOW TRIGGERS;

Output

Trigger: before_employee_insert
Event: INSERT
Table: employees
Statement: BEGIN
  SET NEW.salary = IFNULL(NEW.salary, 0);
END
Timing: BEFORE
Created: (timestamp)
Definer: (user@host)
SQL Mode: 

Common Pitfalls

• Running SHOW TRIGGERS in the wrong database will show no triggers or triggers from that database only.
• Not having proper privileges can prevent you from seeing triggers.
• Using SHOW TRIGGERS does not show triggers from other databases unless you switch to them.
• For detailed info, querying information_schema.TRIGGERS requires correct filtering by TRIGGER_SCHEMA.

sql

/* Wrong: No database selected or wrong database */
SHOW TRIGGERS;

/* Right: Select the database first */
USE your_database;
SHOW TRIGGERS;

Quick Reference

Command	Description
SHOW TRIGGERS;	Lists all triggers in the current database.
SELECT * FROM information_schema.TRIGGERS WHERE TRIGGER_SCHEMA = DATABASE();	Shows detailed trigger info for current database.
USE database_name;	Switch to the database where triggers are defined.
DELIMITER $$ ... DELIMITER ;	Used to define triggers with multiple statements.

Key Takeaways

Use SHOW TRIGGERS to list all triggers in the current MySQL database.

Query information_schema.TRIGGERS for detailed trigger information.

Always select the correct database before running SHOW TRIGGERS.

Ensure you have proper privileges to view triggers.

Use DELIMITER to define triggers with multiple statements.