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

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

select trigger_schema,trigger_name from information_schema.triggers;

让我们按以上语法实现以获取所有触发器名称 along with schema −

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)

如果你只想要当前数据库触发器，则可以使用以下查询。假设当前数据库为“web” −

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)

更新时间：2019-08-22

221 次浏览

开启您的 职业生涯

通过完成课程获得认证

开始学习
广告