MySQL觸發器管理

在本教學中，您將學習如何管理觸發器，包括在MySQL資料庫中顯示，修改和刪除觸發器。

創建觸發器後，可以在包含觸發器定義檔的數據檔夾中顯示其定義。觸發器作為純文本檔存儲在以下資料庫檔夾中：

/data_folder/database_name/table_name.trg

也可通過查詢information_schema資料庫中的triggers表來顯示觸發器，如下所示：

SELECT
    *
FROM
    information_schema.triggers
WHERE
    trigger_schema = 'database_name'
        AND trigger_name = 'trigger_name';

該語句允許您查看觸發器的內容及其元數據，例如：關聯表名和定義器，這是創建觸發器的MySQL用戶的名稱。

如果要檢索指定資料庫中的所有觸發器，則需要使用以下SELECT語句從information_schema資料庫中的triggers表查詢數據：

SELECT
    *
FROM
    information_schema.triggers
WHERE
    trigger_schema = 'database_name';

要查找與特定表相關聯的所有觸發器，請使用以下查詢：

SELECT
    *
FROM
    information_schema.triggers
WHERE
    trigger_schema = 'database_name'
        AND event_object_table = 'table_name';

例如，以下查詢語句與zaixiandb資料庫中的employees表相關聯的所有觸發器。

SELECT * FROM information_schema.triggers
WHERE trigger_schema = 'zaixiandb'
        AND event_object_table = 'employees';

執行上面查詢，得到以下結果 -

mysql> SELECT * FROM information_schema.triggers
WHERE trigger_schema = 'zaixiandb'
        AND event_object_table = 'employees';
+-----------------+----------------+------------------------+--------------------+----------------------+---------------------+--------------------+--------------+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+---------------+----------------------------+----------------------------+--------------------------+--------------------------+------------------------+-----------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
| TRIGGER_CATALOG | TRIGGER_SCHEMA | TRIGGER_NAME           | EVENT_MANIPULATION | EVENT_OBJECT_CATALOG | EVENT_OBJECT_SCHEMA | EVENT_OBJECT_TABLE | ACTION_ORDER | ACTION_CONDITION | ACTION_STATEMENT                                                                                                                                                      | ACTION_ORIENTATION | ACTION_TIMING | ACTION_REFERENCE_OLD_TABLE | ACTION_REFERENCE_NEW_TABLE | ACTION_REFERENCE_OLD_ROW | ACTION_REFERENCE_NEW_ROW | CREATED                | SQL_MODE                                                                          | DEFINER        | CHARACTER_SET_CLIENT | COLLATION_CONNECTION | DATABASE_COLLATION |
+-----------------+----------------+------------------------+--------------------+----------------------+---------------------+--------------------+--------------+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+---------------+----------------------------+----------------------------+--------------------------+--------------------------+------------------------+-----------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
| def             | zaixiandb       | before_employee_update | UPDATE             | def                  | zaixiandb            | employees          |            1 | NULL             | BEGIN
    INSERT INTO employees_audit
    SET action = 'update',
     employeeNumber = OLD.employeeNumber,
        lastname = OLD.lastname,
        changedat = NOW();
END | ROW                | BEFORE        | NULL                       | NULL                       | OLD                      | NEW                      | 2017-08-02 22:06:36.40 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | root@localhost | utf8                 | utf8_general_ci      | utf8_general_ci    |
+-----------------+----------------+------------------------+--------------------+----------------------+---------------------+--------------------+--------------+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+---------------+----------------------------+----------------------------+--------------------------+--------------------------+------------------------+-----------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
1 row in set

MySQL SHOW TRIGGERS語句

在特定資料庫中顯示觸發器的另一種方法是使用SHOW TRIGGERS語句，如下所示：

SHOW TRIGGERS [FROM|IN] database_name
[LIKE expr | WHERE expr];

例如，如果要查看當前資料庫中的所有觸發器，可以使用SHOW TRIGGERS語句，如下所示：

SHOW TRIGGERS;

要獲取特定資料庫中的所有觸發器，請在SHOW TRIGGERS語句中指定資料庫名稱，比如要查詢資料庫：zaixiandb下的所有觸發器，如下所示：

SHOW TRIGGERS FROM zaixiandb;

上面語句返回zaixiandb資料庫中的所有觸發器。

要獲取與特定表相關聯的所有觸發器，可以使用SHOW TRIGGERS語句中的WHERE子句。 以下語句返回與employees表相關聯的所有觸發器：

SHOW TRIGGERS FROM zaixiandb
WHERE `table` = 'employees';

請注意，我們使用反引號包裝table列，因為table是MySQL中的保留關鍵字。

當執行SHOW TRIGGERS語句時，MySQL返回以下列 -

• Trigger：存儲觸發器的名稱，例如before_employee_update觸發器。
• Event：指定事件，例如，調用觸發器的INSERT，UPDATE或DELETE。
• Table：指定觸發器與例如相關聯的表,如employees表。
• Statement：存儲調用觸發器時要執行的語句或複合語句。
• Timing：接受兩個值：BEFORE和AFTER，它指定觸發器的啟動時間。
• Created：在創建觸發器時記錄創建的時間。
• sql_mode：指定觸發器執行時的SQL模式。
• Definer：記錄創建觸發器的帳戶。

請注意，要執行SHOW TRIGGERS語句，您必須具有SUPER許可權。

刪除觸發器

要刪除現有的觸發器，請使用DROP TRIGGER語句，如下所示：

DROP TRIGGER table_name.trigger_name;

例如，如果要刪除與employees表相關聯的before_employees_update觸發器，則可以執行以下語句：

DROP TRIGGER employees.before_employees_update;

要修改觸發器，必須首先刪除它並使用新的代碼重新創建。在MySQL中沒有類似:ALTER TRIGGER語句，因此，您不能像修改其他資料庫對象，如表，視圖和存儲過程那樣修改觸發器。

在本教學中，您已經學會了如何管理觸發器，如在MySQL中顯示，刪除和修改觸發器。