MariaDB(MySQL)でトリガーの作成(CREATE TRIGGER)

MariaDB(MySQL)でトリガーを作成(CREATE TRIGGER)して他のテーブルに同期（作成、更新、削除）する方法を具体的SQLで解説

トリガーを使うとレコード挿入時やレコード更新後、レコード削除後にSQLを実行することが出来ます。
トリガーには、NEW と OLD というMariaDB(MySQL)拡張機能のキーワードがあります。
「NEW.カラム名」は、INSERTやUPDATEしたときの、新く登録又は更新された値にアクセスできます。
「OLD.カラム名」は、UPDATEしたときの更新前の値にアクセスできます。

テスト用データベースとテーブルの作成

説明のためにテスト用データベースとテーブル構造の作成です。

-- データベース作成
CREATE DATABASE IF NOT EXISTS db_test CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE db_test;
-- 同期元テーブル作成
CREATE TABLE t_source(id SERIAL, name VARCHAR(100));
-- 同期先テーブル作成
CREATE TABLE t_dest(id BIGINT, name VARCHAR(100));

レコード挿入時のトリガーの作成

CREATE TRIGGER トリガー名 AFTER INSERT ON テーブル名 FOR EACH ROW BEGIN -- 実行するSQL END;
以下は「t_source」テーブルに新規レコード挿入後に実行されるトリガーを「t_source_insert_trigger」として作成します。

-- 「t_source_insert_trigger」トリガーが存在すれば削除
DROP TRIGGER IF EXISTS t_source_insert_trigger;

DELIMITER //  -- ;を//に一時的に変更する

-- トリガーの作成
CREATE TRIGGER t_source_insert_trigger AFTER INSERT ON t_source FOR EACH ROW
BEGIN 
  INSERT INTO t_dest
  SET
    id=NEW.id,
    name=NEW.name;
END//

DELIMITER ;  -- //を;に戻す

レコード更新後のトリガーの作成

CREATE TRIGGER トリガー名 AFTER UPDATE ON テーブル名 FOR EACH ROW BEGIN -- 実行するSQL END;
以下は「t_source」テーブルのレコード更新後に実行されるトリガーを「t_source_update_trigger」として作成します。

-- 「t_source_update_trigger」トリガーが存在すれば削除
DROP TRIGGER IF EXISTS t_source_update_trigger;

DELIMITER //  -- ;を//に一時的に変更する

-- トリガーの作成
CREATE TRIGGER t_source_update_trigger AFTER UPDATE ON t_source FOR EACH ROW
BEGIN 
  UPDATE t_dest
  SET name=NEW.name
  WHERE id=NEW.id;
END//

DELIMITER ;  -- //を;に戻す

レコード削除後のトリガーの作成

CREATE TRIGGER トリガー名 AFTER DELETE ON テーブル名 FOR EACH ROW BEGIN -- 実行するSQL END;
以下は「t_source」テーブルのレコード削除後に実行されるトリガーを「t_source_delete_trigger」として作成します。

-- 「t_source_update_trigger」トリガーが存在すれば削除
DROP TRIGGER IF EXISTS t_source_delete_trigger;

DELIMITER //  -- ;を//に一時的に変更する

-- トリガーの作成
CREATE TRIGGER t_source_delete_trigger AFTER DELETE ON t_source FOR EACH ROW
BEGIN 
  DELETE FROM t_dest
  WHERE id=OLD.id;
END//

DELIMITER ;  -- //を;に戻す

トリガーを試す

「t_source」テーブルにレコードを挿入したり、更新したり削除したりします。
その後、「t_source」テーブルと「t_dest」テーブルを確認します。

MariaDB [db_test]> INSERT INTO t_source(name) values('最初のレコード'),('次のレコード'),('その次のレコード');
Query OK, 3 rows affected (0.005 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [db_test]> UPDATE t_source SET name='最初のレコード更新' WHERE id=1;
Query OK, 1 row affected (0.003 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [db_test]> DELETE FROM t_source WHERE id=2;
Query OK, 1 row affected (0.002 sec)


MariaDB [db_test]> SELECT * FROM t_source;
+----+-----------------------------+
| id | name                        |
+----+-----------------------------+
|  1 | 最初のレコード更新          |
|  3 | その次のレコード            |
+----+-----------------------------+
2 rows in set (0.001 sec)


MariaDB [db_test]> SELECT * FROM t_dest;
+------+-----------------------------+
| id   | name                        |
+------+-----------------------------+
|    1 | 最初のレコード更新          |
|    3 | その次のレコード            |
+------+-----------------------------+
2 rows in set (0.001 sec)

「t_source」テーブルと「t_dest」テーブルのレコードが一致していることが確認できます。