MySQL觸發器管理 - tw511教學網

在本教學中，您將學習如何管理觸發器，包括在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';

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

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

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

mysql> SELECT * FROM information_schema.triggers
WHERE trigger_schema = 'yiibaidb'
        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             | yiibaidb       | before_employee_update | UPDATE             | def                  | yiibaidb            | 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語句中指定資料庫名稱，比如要查詢資料庫：yiibaidb下的所有觸發器，如下所示：

SHOW TRIGGERS FROM yiibaidb;

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

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

SHOW TRIGGERS FROM yiibaidb
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中顯示，刪除和修改觸發器。