MySQL trigger 介紹 – Zeroplex 生活隨筆

最近在研究 DBMS 的 trigger，不同 DBMS 的 trigger 的功能上差異不小，這裡僅對 MySQL 的 trigger 做筆記。

trigger 的意思同詞一樣，設計目的是讓 DB 的特定事件發生以後，去執行指定的動作。例如某 table 新增了一筆資料以後，將該筆資料記錄到另一個 table。

這次先使用「當使用者註冊成功以後，在 audit log 留下使用者 ID 和註冊的時間。先來建立 users 和 user_audit_log 這二個 table：

CREATE TABLE `users` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `user_name` VARCHAR(200) NOT NULL,
    `mail` varchar(255) NOT NULL
);

CREATE TABLE `user_audit_log` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `user_name` VARCHAR(200) NOT NULL,
    `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP()
);

然後來看一下 trigger 的語法，trigger 可以針對 insert / update / delete 這幾個動作作出反應，另外也可以將 trigger 的動作放在以上幾個動作的之前 BEFORE 與之後 AFTER 執行，也就是說總共有以下這 6 種 trigger：

BEFORE INSERT
BEFORE UPDATE
BEFORE DELETE
AFTER INSERT
AFTER UPDATE
AFTER DELETE

所以要做出「當 user 新增後，將 user_name 複製到 user_audit_load 中」，會使用到的是 AFTER INSERT 的 trigger，語法如下：

CREATE TRIGGER `update_audit_log_after_user_insert` BEFORE INSERT ON `users`
FOR EACH ROW
    .... ;

另外，若 trigger 中需要的動作超過一個時，必須用 BEGIN ... END 將 SQL 刮起來：

CREATE TRIGGER `update_audit_log_after_user_insert` AFTER INSERT ON `users`
FOR EACH ROW
BEGIN
    .... ;
    .... ;
END;

後面範例會有 SELECT 和 INSERT 二個動作，所以會使用到 BEGIN ... END 語法。
下一步，將「當 user 新增後，將 user_name 複製到 user_audit_load 中」這個動作寫進 trigger 當中。下方範例是使用 laset_insert_id() 的方式來取得最新一筆的 user id，這個方法不好，會在後面幾個步驟改掉。先看 trigger 寫法：

CREATE TRIGGER `update_audit_log_after_user_insert`
AFTER INSERT ON `users`
FOR EACH ROW
BEGIN
    -- 找出最新的 user name，存在變數 @name 中
    SET @name = (
        SELECT `user_name`
        FROM `users`
        WHERE `id` = last_insert_id()
    );

    -- 將 user name 存進 user_audit_log 中
    INSERT INTO `user_audit_log` (
        `user_name`
    ) VALUES (
        @name
    );
END;

由上方可以看到 trigger 被觸發時，總共會執行二個動作，所以需要使用 BEGIN ... END 將動作包起來。

新增上方的 trigger 時，MySQL 會報錯誤，這樣才正確。上方的寫法會讓 MySQL 搞不清楚哪些是 trigger 要做的動作、哪些是與 trigger 無關的 SQL 語句，因此這邊要使用 DELIMITER 暫時將原本代表語句結束的 ; 換成 $$ (換成什麼可以自己決定，不要重複即可)：

DELIMITER $$
CREATE TRIGGER `update_audit_log_after_user_insert`
AFTER INSERT ON `users`
FOR EACH ROW
BEGIN
    SET @name = (
        SELECT `user_name`
        FROM `users`
        WHERE `id` = last_insert_id()
    );

    INSERT INTO `user_audit_log` (
        `user_name`
    ) VALUES (
        @name
    );
END;
$$
DELIMITER ;

到這裡，就可以順利的新增一個 trigger 到 table users 上。

回來討論一下目前的寫法有什麼問題。第一個是 last_insert_id() 只能抓到 insert 成功以後的最後一個 ID，但若有一個 transaction 裡面包含多個 insert 時，AFTER INSERT 這個 trigger 裡面就只會抓到一個 ID (transaction 中最後一個 insert 的 user ID)，這樣 trigger 的行為就和我們預期的不同。

幸好 MySQL 特別提供了一個 row 的 alias「NEW」和「OLD」(僅能在 trigger 中使用)。當 table users 一口氣新增 3 筆資料時，會觸發 trigger 執行三次，這時在 trigger 中使用 NEW 就會自動指向特定的 row (若 trigger 是觸發點是 AFTER DELETE 時就要改用 OLD，以此類推)。

DELIMITER $$
CREATE TRIGGER `update_audit_log_after_user_insert`
AFTER INSERT ON `users`
FOR EACH ROW
BEGIN
    -- 使用 NEW 來改寫
    SET @name = NEW.user_name;

    INSERT INTO `user_audit_log` (
        `user_name`
    ) VALUES (
        @name
    );
END;
$$
DELIMITER ;

@name 只被用了一次，繼續化簡：

DELIMITER $$
CREATE TRIGGER `update_audit_log_after_user_insert`
AFTER INSERT ON `users`
FOR EACH ROW
BEGIN
    -- @name 只用了一次，所以這個 query 的動作可以不用另外寫
    -- SET @name = NEW.user_name;

    -- 直接從 NEW 來指定要 insert 的資料即可
    INSERT INTO `user_audit_log` (
        `user_name`
    ) VALUES (
        NEW.user_name
    );
END;
$$
DELIMITER ;

化簡後得到以下的 SQL，這就是最終完整版啦：

DELIMITER $$
CREATE TRIGGER `update_audit_log_after_user_insert`
AFTER INSERT ON `users`
FOR EACH ROW
BEGIN
    INSERT INTO `user_audit_log` (
        `user_name`
    ) VALUES (
        NEW.user_name
    );
END;
$$
DELIMITER ;

這篇文章有幫上你的忙嗎？請考慮小額贊助。

發佈留言 取消回覆

發佈留言取消回覆