すべての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)

現在のデータベーストリガーのみが必要な場合は、以下のクエリを使用できます。現在のデータベースが「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)