MySQL डेटाबेस में सभी ट्रिगर कैसे सूचीबद्ध करें?

ट्रिगर देखने के दो तरीके

MySQL में परिभाषित सभी ट्रिगर देखने के दो मुख्य तरीके हैं: 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 |
+----------------+--------+----------------------+--------------------------------------------------------------------+--------+------------------------+--------------------------------------------+---------+----------------------+----------------------+--------------------+
| 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 |
+----------------+--------+----------------------+--------------------------------------------------------------------+--------+------------------------+--------------------------------------------+---------+----------------------+----------------------+--------------------+

विधि 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 |
| business       | binsertTrigger             | 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)

उपयोगी फ़िल्टर क्वेरीज़

-- विशिष्ट डेटाबेस के ट्रिगर
SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA = 'mydb';

-- विशिष्ट टेबल के ट्रिगर
SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE EVENT_OBJECT_TABLE = 'mytable';

-- केवल BEFORE ट्रिगर
SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE ACTION_TIMING = 'BEFORE';

आवश्यकता: MySQL 5.0.10 या उच्चतर। INFORMATION_SCHEMA विधि अधिक लचीली है क्योंकि यह मानक SELECT सिंटैक्स का उपयोग करती है।