แสดงรายการ Trigger ทั้งหมดในฐานข้อมูล MySQL

MySQL มี 2 วิธีหลักในการดูรายการ Trigger: คำสั่ง SHOW TRIGGERS และการสอบถามจาก INFORMATION_SCHEMA.TRIGGERS

วิธีที่ 1: SHOW TRIGGERS

mysql> SHOW TRIGGERS;

คืนค่าคอลัมน์ครบถ้วน: Trigger, Event, Table, Statement, Timing, Created, sql_mode, Definer, character_set_client, collation_connection, Database Collation

+----------------+--------+----------------------+--------------------------------------------------------------------+--------+------------------------+--------------------------------------------+---------+----------------------+----------------------+--------------------+
| Trigger        | Event  | Table                | Statement                                                          | Timing | Created                | sql_mode                                   | Definer | character_set_client | collation_connection | Database Collation |
+----------------+--------+----------------------+--------------------------------------------------------------------+--------+------------------------+--------------------------------------------+---------+----------------------+----------------------+--------------------+
| binsertTrigger | INSERT | functiontriggersdemo | SET new.Id = uuid()                                                | BEFORE | 2018-10-16 13:36:09.86 | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | root@%  | cp850                | cp850_general_ci     | utf8mb4_unicode_ci |
| Table1Trigger  | INSERT | table1               | begin insert into Table2(id, name) values (new.id, new.name); end  | AFTER  | 2018-10-23 10:51:31.76 | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | root@%  | cp850                | cp850_general_ci     | utf8mb4_unicode_ci |
| insertBef      | INSERT | tblfunctiontrigger   | SET new.id = uuid()                                                | BEFORE | 2018-10-16 13:44:10.93 | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | root@%  | cp850                | cp850_general_ci     | utf8mb4_unicode_ci |
+----------------+--------+----------------------+--------------------------------------------------------------------+--------+------------------------+--------------------------------------------+---------+----------------------+----------------------+--------------------+
3 rows in set (0.13 sec)

วิธีที่ 2: INFORMATION_SCHEMA.TRIGGERS (แนะนำ)

ต้องใช้ MySQL 5.0.10 ขึ้นไป ให้ข้อมูลที่อ่านง่ายและกรองได้ยืดหยุ่นกว่า

mysql> SELECT trigger_schema, trigger_name, action_statement 
    -> FROM information_schema.triggers;

+----------------+----------------------------+-------------------------------------------------------------------------------------------------------------------+
| TRIGGER_SCHEMA | TRIGGER_NAME               | ACTION_STATEMENT                                                                                                  |
+----------------+----------------------------+-------------------------------------------------------------------------------------------------------------------+
| sys            | sys_config_insert_set_user | BEGIN IF @sys.ignore_sys_config_triggers != true AND NEW.set_by IS NULL THEN SET NEW.set_by = USER(); END IF; END |
| sys            | sys_config_update_set_user | BEGIN IF @sys.ignore_sys_config_triggers != true AND NEW.set_by IS NULL THEN SET NEW.set_by = USER(); END IF; END |
| business       | binsertTrigger             | SET new.Id = uuid()                                                                                               |
| business       | insertBef                  | SET new.id = uuid()                                                                                               |
| business       | Table1Trigger              | begin insert into Table2(id, name) values (new.id, new.name);end                                                 |
+----------------+----------------------------+-------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)

คอลัมน์ที่สำคัญใน INFORMATION_SCHEMA.TRIGGERS

ตัวอย่าง: ดู Trigger ของฐานข้อมูลเฉพาะ

SELECT trigger_name, event_manipulation, event_object_table, action_timing, action_statement
FROM information_schema.triggers
WHERE trigger_schema = 'my_database';