資料結構
網路
RDBMS
作業系統
Java
MS Excel
iOS
HTML
CSS
Android
Python
C 程式設計
C++
C#
MongoDB
MySQL
Javascript
PHP
物理
化學
生物
數學
英語
經濟
心理
社會研究
時尚研究
法律研究如何獲取所有 MySQL 觸發器和僅適用於當前資料庫的觸發器
要獲取所有 MySQL 觸發器，以下是語法 −
select trigger_schema,trigger_name from information_schema.triggers;
讓我們按以上語法實現以獲取所有觸發器名稱 along with schema −
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)
更新時間：2019-08-22
221 次瀏覽
廣告