資料結構
網路
RDBMS
作業系統
Java
MS Excel
iOS
HTML
CSS
Android
Python
C 程式設計
C++
C#
MongoDB
MySQL
Javascript
PHP
物理
化學
生物
數學
英語
經濟學
心理學
社會研究
時尚研究
法律研究如何在 MySQL 資料庫中列出所有觸發器？
要列出 MySQL 資料庫中的所有觸發器，可以使用 SHOW 命令。查詢如下 −
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 | | 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)
您可以藉助以下查詢訪問 information_schema 表。
注意 − 您需要具備 MySQL 版本 5.0.10 及以上版本。我目前使用的是 MySQL 8.0.12 版本 −
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)
更新於: 2020 年 6 月 29 日
453 次瀏覽
廣告