The SHOW TRIGGERS statement displays the triggers defined for tables in a database.
Syntax
SHOW TRIGGERS
[{FROM | IN} database_name]
[LIKE 'pattern' | WHERE expr]
Parameters
Parameter |
Note |
|---|---|
| FROM | IN database_name | Optional. Specifies the name of the database for which you want to view triggers. If not specified, triggers in the default database are displayed. |
| LIKE 'pattern' | Optional. Specifies the table name pattern to match (not the trigger name). You can use % and _ A wildcard. Displays triggers that match the table. |
| WHERE expr | Optional. Use the WHERE clause to specify conditions for selecting rows. |
The SHOW TRIGGERS statement returns results only for databases and tables for which the TRIGGER privilege is granted.
The SHOW TRIGGERS statement outputs the following information:
Trigger: The name of the trigger.Event: The trigger event. This specifies the type of operation that activates the trigger on the associated table. Valid values areINSERT(a row was inserted),DELETE(a row was deleted), orUPDATE(a row was modified).Table: the table that defines the trigger.Statement: The statements executed when the trigger is activated.Timing: Indicates whether the trigger is activated before or after the triggering event. The value can beBEFOREorAFTER.Created: The date and time when the trigger was created. The value is of theTIMESTAMP(2)type (with milliseconds as the fractional part).sql_mode: The SQL mode that takes effect when the trigger is created.Definer: the user account that creates the trigger, in the format of'user_name'@'host_name'.character_set_client: The value of thecharacter_set_clientsystem variable in the current session when the trigger is created.collation_connection: The value of thecollation_connectionsystem variable in the current session when the trigger was created.Database Collation: The collation of the database associated with the trigger.
Trigger information can also be obtained from the INFORMATION_SCHEMA TRIGGERS table. For more information, see INFORMATION_SCHEMA TRIGGERS.
Examples
View triggers on all tables in the current database.
obclient> SHOW TRIGGERS;
View triggers in a specified database:
obclient> SHOW TRIGGERS FROM test_db;
View triggers that match a specific table name pattern:
obclient> SHOW TRIGGERS LIKE 'test%'\G
The query result is as follows:
*************************** 1. row ***************************
Trigger: test_trg
Event: UPDATE
Table: test
Statement: TRIGGER test_trg BEFORE UPDATE ON test FOR EACH ROW
BEGIN
IF NEW.user_num < 1 THEN
SET NEW.user_num = 1;
ELSEIF NEW.user_num > 45 THEN
SET NEW.user_num = 45;
END IF;
END
Timing: BEFORE
Created: 2025-12-23 16:12:32.41
sql_mode: STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER
Definer: root@%
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
Database Collation: utf8mb4_general_ci
1 row in set (0.026 sec)