Triggers in MySQL are stored programs similar to procedures. These can be created on a table, schema, view and database that are associated with an event and whenever an event occurs the respective trigger is invoked.
Triggers are, in fact, written to be executed in response to any of the following events −
- A database manipulation (DML) statement (DELETE, INSERT, or UPDATE).
- A database definition (DDL) statement (CREATE, ALTER, or DROP).
- A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).
The MySQL SHOW TRIGGERS Statement is used to display information about all the triggers defined in the current database.
Syntax
Following is the syntax of the MySQL SHOW TRIGGERS Statement −
SHOW TRIGGERS
[{FROM | IN} db_name]
[LIKE 'pattern' | WHERE expr]
Example
Assume we have created a table with name student as shown below −
mysql> Create table Student(Name Varchar(35), age INT, Score INT); Query OK, 0 rows affected (2.89 sec)
Following query creates a trigger sample_trigger which will set the score value 0 if you enter a value that is less than 0 as score.
mysql> DELIMITER // mysql> Create Trigger sample_trigger BEFORE INSERT ON student FOR EACH ROW BEGIN IF NEW.score < 0 THEN SET NEW.score = 0; END IF; END // Query OK, 0 rows affected (0.77 sec) mysql> DELIMITER ;
Assume we have created another trigger using the AFTER clause −
mysql> DELIMITER // mysql> CREATE TRIGGER testTrigger AFTER UPDATE ON Student FOR EACH ROW INSERT INTO Student SET action = 'update', Name = OLD.Name, age = OLD.age, score = OLD.score; Query OK, 0 rows affected (0.57 sec) mysql> DELIMITER ;
Following query lists out the triggers in the current database −
mysql> SHOW TRIGGERS \G; *************************** 1. row *************************** Trigger: sample_trigger Event: INSERT Table: student Statement: BEGIN IF NEW.score < 0 THEN SET NEW.score = 0; END IF; END Timing: BEFORE Created: 2021-05-12 19:08:04.50 sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION Definer: root@localhost character_set_client: utf8mb4 collation_connection: utf8mb4_0900_ai_ci Database Collation: utf8mb4_0900_ai_ci *************************** 2. row *************************** Trigger: testTrigger Event: UPDATE Table: student Statement: INSERT INTO Student SET action = 'update', Name = OLD.Name, age = OLD.age, score = OLD.score Timing: AFTER Created: 2021-05-12 19:10:44.49 sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION Definer: root@localhost character_set_client: utf8mb4 collation_connection: utf8mb4_0900_ai_ci Database Collation: utf8mb4_0900_ai_ci 2 rows in set (0.45 sec)
FROM or IN clause
You can retrieve the information of triggers from a specific database using the FROM clause.
Example
Assume we have created a database named demo using the CREATE DATABASE statement −
mysql> CREATE DATABASE demo; Query OK, 1 row affected (0.18 sec)
Now, let us create triggers in it, using the CREATE TABLE statement −
mysql> USE demo mysql> DELIMITER // mysql> CREATE TRIGGER testTrigger1 AFTER INSERT ON Student FOR EACH ROW INSERT INTO Student SET action = 'INSERT'; Query OK, 0 rows affected (0.37 sec) mysql> CREATE TRIGGER testTrigger2 AFTER INSERT ON Student FOR EACH ROW INSERT INTO Student SET action = 'INSERT'; Query OK, 0 rows affected (0.18 sec) mysql> CREATE TRIGGER testTrigger3 AFTER INSERT ON Student FOR EACH ROW INSERT INTO Student SET action = 'INSERT'; Query OK, 0 rows affected (0.64 sec) mysql> DELIMITER ;
Following query lists out the databases in the database “demo” −
mysql> SHOW TABLES FROM demo; +--------------+--------+---------+-----------------------------------------------------+--------+------------------------+--------------------------------------------+----------------+----------------------+----------------------+--------------------+ | Trigger | Event | Table | Statement | Timing | Created | sql_mode | Definer | character_set_client | collation_connection | Database Collation | +--------------+--------+---------+-----------------------------------------------------+--------+------------------------+--------------------------------------------+----------------+----------------------+----------------------+--------------------+ | testTrigger1 | INSERT | student | INSERT INTO Student SET action = 'INSERT' | AFTER | 2021-05-12 19:40:18.41 | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | root@localhost | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci | | testTrigger2 | INSERT | student | INSERT INTO Student SET action = 'INSERT' | AFTER | 2021-05-12 19:41:19.54 | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | root@localhost | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci | | testTrigger3 | INSERT | student | INSERT INTO Student SET action = 'INSERT' | AFTER | 2021-05-12 19:41:41.82 | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | root@localhost | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci | +--------------+--------+---------+-----------------------------------------------------+--------+------------------------+--------------------------------------------+----------------+----------------------+----------------------+--------------------+ 3 rows in set (0.10 sec)
You can also use the IN clause instead of FROM as −
mysql> SHOW TRIGGERS IN demo; +--------------+--------+---------+-----------------------------------------------------+--------+------------------------+--------------------------------------------+----------------+----------------------+----------------------+--------------------+ | Trigger | Event | Table | Statement | Timing | Created | sql_mode | Definer | character_set_client | collation_connection | Database Collation | +--------------+--------+---------+-----------------------------------------------------+--------+------------------------+--------------------------------------------+----------------+----------------------+----------------------+--------------------+ | testTrigger1 | INSERT | student | INSERT INTO Student SET action = 'INSERT' | AFTER | 2021-05-12 19:40:18.41 | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | root@localhost | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci | | testTrigger2 | INSERT | student | INSERT INTO Student SET action = 'INSERT' | AFTER | 2021-05-12 19:41:19.54 | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | root@localhost | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci | | testTrigger3 | INSERT | student | INSERT INTO Student SET action = 'INSERT' | AFTER | 2021-05-12 19:41:41.82 | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | root@localhost | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci | +--------------+--------+---------+-----------------------------------------------------+--------+------------------------+--------------------------------------------+----------------+----------------------+----------------------+--------------------+ 3 rows in set (0.00 sec)
The WHERE clause
You can use the WHERE clause of the SHOW TRIGGERS statements to retrieve info about the triggers which match the specified condition.
Example
Assume we have created a table using the SELECT statement as shown below −
mysql> CREATE TABLE data(name VARCHAR(255)); Query OK, 0 rows affected (2.09 sec)
Let us created table triggers on the above table −
mysql> CREATE TRIGGER tgr1 AFTER UPDATE ON data FOR EACH ROW UPDATE data SET action = 'UPDATE'; Query OK, 0 rows affected (0.59 sec) mysql> CREATE TRIGGER tgr2 AFTER INSERT ON data FOR EACH ROW INSERT INTO data SET action = 'INSERT'; Query OK, 0 rows affected (0.30 sec)
Following query retrieves the triggers in the current database whose event is update −
mysql> SHOW TRIGGERS FROM demo WHERE Event = 'UPDATE' \G; *************************** 1. row *************************** Trigger: testTrigger Event: UPDATE Table: student Statement: INSERT INTO Student SET action = 'update', Name = OLD.Name, age = OLD.age, score = OLD.score Timing: AFTER Created: 2021-05-12 19:10:44.49 sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION Definer: root@localhost character_set_client: utf8mb4 collation_connection: utf8mb4_0900_ai_ci Database Collation: utf8mb4_0900_ai_ci 1 row in set (0.00 sec)
0 Comments