MySQL-এ সকল ট্রিগার ও বর্তমান ডেটাবেসের ট্রিগার লিস্ট করা

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

বর্তমান ডেটাবেসের ট্রিগার (SHOW TRIGGERS)

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

SHOW TRIGGERS কলামের অর্থ

• Trigger — ট্রিগার নাম
• Event — INSERT/UPDATE/DELETE
• Table — লক্ষ্য টেবিল
• Statement — এক্সিকিউটেবল SQL
• Timing — BEFORE/AFTER
• Created — তৈরির সময়
• Definer — সিকিউরিটি কনটেক্সট

ফিল্টার করে দেখুন

SHOW TRIGGERS WHERE `Table` = 'DemoTable';