How to get all the MySQL triggers and the triggers for only the current database

To get all the MySQL triggers, following is the syntax −

select trigger_schema,trigger_name from information_schema.triggers;

Let us implement the above syntax to get all the trigger names along with schema −

mysql> select trigger_schema,trigger_name from information_schema.triggers;

This will produce the following output −

+----------------+---------------------------------+
| TRIGGER_SCHEMA | TRIGGER_NAME                    |
+----------------+---------------------------------+
| sys            | sys_config_insert_set_user      |
| sys            | sys_config_update_set_user      |
| business       | binsertTrigger                  |
| business       | insertBef                       |
| business       | Table1Trigger                   |
| test           | insertingTrigger                |
| sample         | InsertPrevent_TriggerDemo       |
| sample         | InsertPreventTrigger            |
| sample         | before_Client_insert            |
| sample         | enforce_phone_check             |
| sample         | default_current_year            |
| sample         | restrictUpdateDemo              |
| web            | lowerCaseOnInsertDemo           |
| web            | preventing_to_insert_zero_value |
+----------------+---------------------------------+
14 rows in set (0.00 sec)

If you want the current database triggers only, you can use below query. Let’s say the current database is “web” −

mysql> use web;
Database changed
mysql> show triggers;

This will produce the following output −

+---------------------------------+--------+--------------+-----------------------------------------------------------------------------------------------------------------------------+--------+------------------------+--------------------------------------------+---------+----------------------+----------------------+--------------------+
| Trigger                        | Event   | Table        | Statement                                                                                                                   | Timing | Created | sql_mode | Definer | character_set_client | collation_connection | Database Collation |
+---------------------------------+--------+--------------+-----------------------------------------------------------------------------------------------------------------------------+--------+------------------------+--------------------------------------------+---------+----------------------+----------------------+--------------------+
| lowerCaseOnInsertDemo         | INSERT | demotable222 | SET NEW.StudentSubject = LOWER(NEW.StudentSubject) | BEFORE | 2019-06-01 15:53:09.29 | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | root@% | utf8 | utf8_general_ci | utf8_unicode_ci |
| preventing_to_insert_zero_value | INSERT | DemoTable | begin
if(new.Value = 0) then SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'You can not provide 0 value';
END if;
end | BEFORE | 2019-07-07 18:03:41.65 | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | root@% | utf8 | utf8_general_ci | utf8_unicode_ci |
+---------------------------------+--------+--------------+-----------------------------------------------------------------------------------------------------------------------------+--------+------------------------+--------------------------------------------+---------+----------------------+----------------------+--------------------+
2 rows in set (0.00 sec)

Updated on: 22-Aug-2019

178 Views

Related Articles

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements