如何获取所有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)