The SHOW CREATE TRIGGER statement displays the CREATE TRIGGER statement used to create the trigger.
Syntax
SHOW CREATE TRIGGER trigger_name
Parameters
| Parameter | Description |
|---|---|
| trigger_name | The name of the trigger to view. You can specify the database name in the database_name.trigger_name format. |
This statement requires the TRIGGER privilege on the tables associated with the trigger.
The SHOW CREATE TRIGGER statement outputs the following information:
Trigger: the name of the trigger.sql_mode: the SQL mode in effect when the trigger is executed.SQL Original Statement: theCREATE TRIGGERstatement used to define the trigger.character_set_client: the value of thecharacter_set_clientsystem variable in the current session when the trigger was 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.Created: the date and time when the trigger was created. The value is of theTIMESTAMP(2)type, which includes the hundredths of a second in the fractional part.
You can also obtain trigger information from the INFORMATION_SCHEMA TRIGGERS table. For more information, see INFORMATION_SCHEMA TRIGGERS.
Examples
View the definition of the trigger.
obclient> CREATE TABLE test(user_num INT);
obclient> DELIMITER //
obclient> CREATE 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 //
obclient> DELIMITER ;
obclient> SHOW CREATE TRIGGER test_trg;
The query result is as follows:
+----------+-------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Trigger | sql_mode | SQL Original Statement | character_set_client | collation_connection | Database Collation |
+----------+-------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| test_trg | STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER | CREATE DEFINER = root@% 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 | utf8mb4 | utf8mb4 | utf8mb4 |
+----------+-------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set