Cách lấy tất cả trigger MySQL và trigger chỉ của cơ sở dữ liệu hiện tại

Để lấy tất cả trigger MySQL, cú pháp:

SELECT trigger_schema, trigger_name FROM information_schema.triggers;

Thực hiện:

mysql> SELECT trigger_schema, trigger_name FROM information_schema.triggers;

Kết quả (ví dụ):

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

Nếu chỉ muốn trigger của cơ sở dữ liệu hiện tại (ví dụ 'web'):

mysql> USE web; Database changed mysql> SHOW TRIGGERS;

Kết quả:

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