The SHOW CREATE TRIGGER statement is used to display the CREATE TRIGGER statement that created a trigger.
Syntax
SHOW CREATE TRIGGER trigger_name
Parameters
Parameter |
Note |
|---|---|
| trigger_name | The name of the trigger to be viewed. You can use database_name.trigger_name Specify the database. |
This statement requires the TRIGGER privilege on the table associated with the trigger.
The SHOW CREATE TRIGGER statement outputs the following information:
Trigger: The name of the trigger.sql_mode: The SQL mode that takes effect when the trigger is executed.SQL Original Statement: TheCREATE TRIGGERstatement that defines 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 is 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 (the fractional part represents hundredths of a second).
Trigger information can also be obtained 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