実行できるSQLiteドキュメント: トリガー

Q: SQLiteのトリガーとは何ですか？

トリガーは、テーブルに対して特定のイベント（INSERT・UPDATE・DELETE）が起きたときに自動で実行されるSQLのかたまりです。CREATE TRIGGERで一度定義しておけば、あとはイベントが発生するたびにSQLite側が勝手に発火してくれます。監査ログを残したり、派生カラムを最新状態に保ったり、アプリ側に頼らずDBレベルでルールを徹底させたいときに便利です。

Q: BEFORE・AFTER・INSTEAD OFトリガーは何が違うのですか？

BEFOREは行が変更される前に走るので、バリデーションや値の整形に向いています。AFTERは変更が確定したあとに走るので、ログ出力や別テーブルへの同期に使います。INSTEAD OFはビュー専用で、本来の操作を完全に置き換えてしまうのが特徴です。これを使うと、通常は更新できないビューに対しても書き込みができるようになります。

Q: トリガーの中で変更対象の行を参照するにはどうしますか？

INSERTとUPDATEでは新しい行をNEW.columnで、UPDATEとDELETEでは既存の行をOLD.columnで参照します。つまりINSERTトリガーではNEWだけ、DELETEトリガーではOLDだけ、UPDATEトリガーでは両方が使えるという仕組みです。これらの参照は、いま処理中の1行に対してのみ有効です。

Q: SQLiteでトリガーの一覧表示や削除はどうやりますか？

トリガーの定義はsqlitemasterに格納されているので、SELECT name, tblname FROM sqlitemaster WHERE type = 'trigger';で一覧を取得できます。削除はDROP TRIGGER triggername;、存在するか自信がないときはDROP TRIGGER IF EXISTS trigger_name;でOKです。なお、テーブルをDROPすると、それに紐づくトリガーも一緒に消えます。

トリガーで SQL を自動実行する

SQLite のトリガー（trigger）は、特定のテーブルで特定のイベントが起きたときに自動で走る SQL のかたまりです。一度書いておけば、「いつ実行するか」は SQLite が面倒を見てくれます。

基本の書き方はこんな感じです。

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL NOT NULL
);

CREATE TABLE price_history (
    product_id INTEGER,
    old_price REAL,
    new_price REAL,
    changed_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER log_price_change
AFTER UPDATE OF price ON products
BEGIN
    INSERT INTO price_history(product_id, old_price, new_price)
    VALUES (OLD.id, OLD.price, NEW.price);
END;

INSERT INTO products(name, price) VALUES ('ノート', 5.00);
UPDATE products SET price = 6.50 WHERE name = 'ノート';
SELECT * FROM price_history;

一度も price_history に対して明示的な INSERT を書いていないのに、トリガーが代わりに記録してくれました。今後は CLI からでもスクリプトからでもアプリからでも、価格を更新するたびに同じように履歴が残ります。

CREATE TRIGGER の構文を分解する

sqlite トリガーの構文を、パーツごとに見ていきましょう。

CREATE TRIGGER trigger_name
{ BEFORE | AFTER | INSTEAD OF } { INSERT | UPDATE [ OF column_list ] | DELETE }
ON table_name
[ FOR EACH ROW ]
[ WHEN condition ]
BEGIN
    -- 1つ以上のステートメント
END;

タイミング — BEFORE は変更前、AFTER は変更後、INSTEAD OF は変更そのものを置き換えます(ビュー専用)。
イベント — トリガーを発火させる操作です。UPDATE OF col1, col2 と書けば、特定の列の更新だけに絞り込めます。
テーブル — 監視対象のテーブルです。
FOR EACH ROW — SQLite は行単位のトリガーしかサポートしていないため、これは暗黙的に適用されます。明示的に書いても構いませんが、挙動は変わりません。
WHEN — 任意の条件式です。これが真のときだけトリガー本体が実行されます。
本体 — BEGIN と END の間に書く 1 つ以上の文です。それぞれセミコロンで終える必要があります。

文法はこれだけです。実用的なトリガーはたいてい 5〜10 行で収まります。

OLD と NEW:変更対象の行を参照する

トリガー本体の中では、2 つの擬似的な行を通して対象データを参照できます。

NEW — 新しく入ってくる行。INSERT と UPDATE のトリガーで使えます。
OLD — 既存の行。UPDATE と DELETE のトリガーで使えます。

DELETE トリガーでは OLD のみ、INSERT トリガーでは NEW のみが使えます。UPDATE トリガーでは両方が利用可能です。

CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    name TEXT,
    balance REAL NOT NULL
);

CREATE TABLE deletions (
    table_name TEXT,
    row_id INTEGER,
    snapshot TEXT,
    deleted_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER archive_account
AFTER DELETE ON accounts
BEGIN
    INSERT INTO deletions(table_name, row_id, snapshot)
    VALUES ('accounts', OLD.id, OLD.name || ' / ' || OLD.balance);
END;

INSERT INTO accounts(name, balance) VALUES ('Rosa', 120.00);
DELETE FROM accounts WHERE name = 'Rosa';
SELECT * FROM deletions;

accountsからは行が消えていますが、消える直前のデータがdeletionsにしっかり残っています。

BEFORE トリガー：行を検証・補正する

BEFOREトリガーは、行の変更がディスクに書き込まれる前に発火します。エラーを発生させたり、データを正規化したりするのに便利です。

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL,
    created_at TEXT
);

CREATE TRIGGER lowercase_email
BEFORE INSERT ON users
BEGIN
    UPDATE users SET email = email; -- 何もしない、文脈を示すだけ
END;

CREATE TRIGGER reject_empty_email
BEFORE INSERT ON users
WHEN NEW.email IS NULL OR trim(NEW.email) = ''
BEGIN
    SELECT RAISE(ABORT, 'メールアドレスは空にできません');
END;

INSERT INTO users(email) VALUES ('ada@example.com');
INSERT INTO users(email) VALUES ('');
SELECT * FROM users;

2 つ目の INSERT は、行が 1 件も書き込まれる前に中断されます。RAISE(ABORT, '...') は現在のステートメントをキャンセルして、その開始時点までロールバックします。挙動をもっと細かく制御したい場合は、RAISE(FAIL, ...)、RAISE(ROLLBACK, ...)、RAISE(IGNORE) を使い分けられます。

純粋なデータ検証であれば、CHECK 制約のほうがおすすめです。宣言的に書けますし、オプティマイザにも認識されます。BEFORE トリガーの出番は、他のテーブルを参照する必要があるときや、CHECK では表現できないルールを書きたいときです。

WHEN 句で条件付きトリガーを作る

WHEN 句を使うと、どの行の変更でトリガー本体を発火させるかをフィルタリングできます。判定は行ごとに、OLD と NEW がバインドされた後に評価されます。

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    total REAL NOT NULL,
    status TEXT NOT NULL
);

CREATE TABLE big_orders (
    order_id INTEGER,
    total REAL,
    flagged_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER flag_big_order
AFTER INSERT ON orders
WHEN NEW.total >= 1000
BEGIN
    INSERT INTO big_orders(order_id, total) VALUES (NEW.id, NEW.total);
END;

INSERT INTO orders(total, status) VALUES (50, 'paid');
INSERT INTO orders(total, status) VALUES (1500, 'paid');
INSERT INTO orders(total, status) VALUES (2200, 'paid');
SELECT * FROM big_orders;

最初の注文は条件を満たしません。残り2件は通過します。WHEN句を付けないと、すべてのインサートがbig_ordersに書き込まれてしまい、読み出し側で絞り込む羽目になります。

INSTEAD OF トリガー：ビューに書き込めるようにする

ビューはデフォルトでは読み取り専用です。そこでINSTEAD OFトリガーの出番です。ビューへの書き込みを横取りして、代わりに自前のSQLを実行します。たいていは元のテーブルへの書き込みに変換する形になります。

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    salary REAL
);

CREATE VIEW employee_summary AS
SELECT id, first_name || ' ' || last_name AS full_name, salary
FROM employees;

CREATE TRIGGER employee_summary_insert
INSTEAD OF INSERT ON employee_summary
BEGIN
    INSERT INTO employees(first_name, last_name, salary)
    VALUES (
        substr(NEW.full_name, 1, instr(NEW.full_name, ' ') - 1),
        substr(NEW.full_name, instr(NEW.full_name, ' ') + 1),
        NEW.salary
    );
END;

INSERT INTO employee_summary(full_name, salary) VALUES ('Boris Petrov', 60000);
SELECT * FROM employees;

アプリケーションからは普通のテーブルのように View にアクセスするだけで、first_name と last_name への分割はトリガーが裏で面倒を見てくれます。

トリガー一覧の確認と削除

トリガーはテーブルやインデックスと同じく sqlite_master に格納されています。

CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT);

CREATE TRIGGER touch_note
AFTER UPDATE ON notes
BEGIN
    SELECT 1; -- プレースホルダー本体
END;

SELECT name, tbl_name, sql
FROM sqlite_master
WHERE type = 'trigger';

DROP TRIGGER IF EXISTS touch_note;

SELECT count(*) AS remaining
FROM sqlite_master
WHERE type = 'trigger';

DROP TRIGGER IF EXISTS name; が安全な書き方です。トリガーが紐づいているテーブルを削除すれば、トリガーも自動的に消えるので、わざわざ先に片付ける必要はありません。

ハマりやすいポイント

最初にやらかしがちな落とし穴をいくつか挙げておきます。

トリガーは行単位で発火する（文単位ではない）。 1,000 行を対象にした UPDATE なら、トリガーは 1,000 回走ります。本体の処理が重いと、コストはあっという間に膨らみます。
トリガーは外側のトランザクションの中で動く。 外側の文がロールバックされれば、トリガーが書いた内容も一緒に巻き戻ります。普通はそれで都合がいいのですが、「何があってもログは残したい」という用途には使えないということです。
再帰的トリガーはデフォルトでオフ。 同じテーブルを書き換えるトリガーは、PRAGMA recursive_triggers = ON; を指定しない限り自分自身を再発火させません。明確な理由がない限りオフのままにしておきましょう。
アプリ側の書き込みからすり抜けるには、DB を経由しないしかない。 SQLite を通る書き込みである以上、トリガーは必ず走ります。生 SQL でバッチ処理する ORM 経由でも発火します。
業務ロジックを大量のトリガーに分散させない。 呼び出し側からは見えないので、「なんでこの行ができたんだ？」とデバッグする人は sqlite_master を grep する羽目になります。監査ログ、派生カラム、ビューへの書き込み対応といった横断的な用途に絞り、それ以外はアプリ側のコードに残しましょう。

実践的な監査ログの例

ここまでのパターンを組み合わせて、posts テーブルへの変更をすべて記録してみます。

CREATE TABLE posts (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    body TEXT,
    updated_at TEXT
);

CREATE TABLE post_audit (
    post_id INTEGER,
    action TEXT,
    old_title TEXT,
    new_title TEXT,
    at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER posts_audit_update
AFTER UPDATE OF title, body ON posts
BEGIN
    UPDATE posts SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
    INSERT INTO post_audit(post_id, action, old_title, new_title)
    VALUES (NEW.id, 'update', OLD.title, NEW.title);
END;

INSERT INTO posts(title, body) VALUES ('下書き', '初稿');
UPDATE posts SET title = '公開済み' WHERE id = 1;
SELECT id, title, updated_at FROM posts;
SELECT * FROM post_audit;

トリガーを1つ仕込んでおけば、updated_at の更新と監査ログの書き込みを一か所にまとめられます。UPDATE を実行するアプリ側のコードは、その存在をまったく意識する必要がありません。

次回: JSON サポート

トリガーは行に対するイベントまわりの自動化を担うものでした。次に紹介する SQLite の応用機能は、行の中身に何を入れられるか、つまり JSON です。SQLite には JSON 用の関数が一通り揃っていて、SQL の世界から出ることなく構造化データを検索・更新できます。詳しくは次のページで見ていきましょう。

よくある質問

SQLiteのトリガーとは何ですか？

トリガーは、テーブルに対して特定のイベント（INSERT・UPDATE・DELETE）が起きたときに自動で実行されるSQLのかたまりです。CREATE TRIGGERで一度定義しておけば、あとはイベントが発生するたびにSQLite側が勝手に発火してくれます。監査ログを残したり、派生カラムを最新状態に保ったり、アプリ側に頼らずDBレベルでルールを徹底させたいときに便利です。

BEFORE・AFTER・INSTEAD OFトリガーは何が違うのですか？

BEFOREは行が変更される前に走るので、バリデーションや値の整形に向いています。AFTERは変更が確定したあとに走るので、ログ出力や別テーブルへの同期に使います。INSTEAD OFはビュー専用で、本来の操作を完全に置き換えてしまうのが特徴です。これを使うと、通常は更新できないビューに対しても書き込みができるようになります。

トリガーの中で変更対象の行を参照するにはどうしますか？

INSERTとUPDATEでは新しい行をNEW.columnで、UPDATEとDELETEでは既存の行をOLD.columnで参照します。つまりINSERTトリガーではNEWだけ、DELETEトリガーではOLDだけ、UPDATEトリガーでは両方が使えるという仕組みです。これらの参照は、いま処理中の1行に対してのみ有効です。

SQLiteでトリガーの一覧表示や削除はどうやりますか？

トリガーの定義はsqlite_masterに格納されているので、SELECT name, tbl_name FROM sqlite_master WHERE type = 'trigger';で一覧を取得できます。削除はDROP TRIGGER trigger_name;、存在するか自信がないときはDROP TRIGGER IF EXISTS trigger_name;でOKです。なお、テーブルをDROPすると、それに紐づくトリガーも一緒に消えます。

SQLiteトリガー入門：CREATE TRIGGERとOLD/NEWの使い方