MySQL में ट्रिगर सूचीबद्ध करना
सभी डेटाबेस के ट्रिगर देखने के लिए information_schema.triggers टेबल क्वेरी करें, या वर्तमान डेटाबेस के लिए SHOW 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)
वर्तमान डेटाबेस के ट्रिगर देखना
पहले डेटाबेस चुनें, फिर 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 | +---------------------------------+--------+--------------+-----------------------------------------------------------------------------------------------------------------------------+--------+------------------------+--------------------------------------------+---------+----------------------+----------------------+--------------------+ 2 rows in set (0.00 sec)
नोट: SHOW TRIGGERS विस्तृत जानकारी देता है: इवेंट प्रकार (INSERT/UPDATE/DELETE), टाइमिंग (BEFORE/AFTER), कथन, निर्माण समय, डिफाइनर, आदि।