如何获取所有MySQL触发器以及仅适用于当前数据库的触发器

要获取所有MySQL触发器，以下是语法-

select trigger_schema,trigger_name from information_schema.triggers;

让我们实现以上语法以获取所有触发器名称以及模式-

mysql> select trigger_schema,trigger_name from information_schema.triggers;

这将产生以下输出-

+----------------+---------------------------------+
| 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)

如果只希望当前数据库触发器，则可以使用以下查询。假设当前数据库是“网络”-

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

这将产生以下输出-

+---------------------------------+--------+--------------+-----------------------------------------------------------------------------------------------------------------------------+--------+------------------------+--------------------------------------------+---------+----------------------+----------------------+--------------------+
| 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)