PostgreSQLのトリガータイプ: BEFORE, AFTER, INSTEAD OF

PostgreSQLのトリガーは主に3つのカテゴリに分かれるんだ：

1. BEFORE — メインの操作（例えばINSERT、UPDATE、DELETE）の前に実行される。操作を止めたり、保存前にデータを変更したりできるよ。

2. AFTER — メインの操作が終わった後に実行される。このタイプはログ記録や関連レコードの作成、操作が成功した後にやりたいことに使われることが多い。

3. INSTEAD OF — 実際の操作の代わりに実行される。これはビュー専用。例えば、ユーザーがビューにデータを挿入しようとした時、INSTEAD OFトリガーでその処理をコントロールできる。

BEFOREトリガー

BEFOREトリガーは、PostgreSQLがメインの操作を実行する前に発動する。データを保存する直前にチェックや変更をしたい時に便利。飛行機に乗る前の荷物チェックみたいな感じで、ダメなら修正したりブロックしたりできる。

データ挿入前のバリデーションの例を見てみよう。 例えばstudentsテーブルがあって、学生の情報を保存してるとする。学生の年齢が100歳を超えないようにしたい（まあ、めったにないけどね）。

テーブルを作成：

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    age INT NOT NULL
);

トリガー用の関数を作成：

CREATE OR REPLACE FUNCTION validate_age()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.age > 100 THEN
        RAISE EXCEPTION '学生の年齢は100歳を超えちゃダメだよ！';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

トリガーを作成：

CREATE TRIGGER before_insert_students
BEFORE INSERT ON students
FOR EACH ROW
EXECUTE FUNCTION validate_age();

これで、もし100歳超えの学生を挿入しようとすると、PostgreSQLがエラーを返す：

INSERT INTO students (name, age) VALUES ('イワン イワノフ', 120);
-- エラー: 学生の年齢は100歳を超えちゃダメだよ！

こんな感じでチェックできる！

AFTERトリガー

AFTERトリガーは、メインの操作が無事に終わった後に発動する。操作の結果に依存する処理（例えばログ記録や関連レコードの作成）に便利。

シナリオ：studentsテーブルがあって、すべての変更を別のログテーブルに記録したい。

ログ用テーブルを作成：

CREATE TABLE students_log (
    id SERIAL PRIMARY KEY,
    student_id INT,
    operation TEXT,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

トリガー用の関数を作成：

CREATE OR REPLACE FUNCTION log_student_changes()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO students_log (student_id, operation)
    VALUES (NEW.id, TG_OP);  -- TG_OPには操作タイプ（INSERT, UPDATE, DELETE）が入る
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

トリガーを作成：

CREATE TRIGGER after_insert_students
AFTER INSERT ON students
FOR EACH ROW
EXECUTE FUNCTION log_student_changes();

これで新しい学生を追加すると、自動的に操作がログに記録される：

INSERT INTO students (name, age) VALUES ('アンナ リン', 22);

SELECT * FROM students_log;
-- 結果:
-- id | student_id | operation |        timestamp
--  1 |          1 | INSERT    | 2023-11-15 12:00:00

INSTEAD OFトリガー

INSTEAD OFトリガーは、操作の代わりに発動する。ビュー（view）でしか使えない唯一のトリガータイプ。ビューに直接できない操作も、これで柔軟に処理できる。

シナリオ：coursesとteachersという2つのテーブルがあるとする。これを結合したビューを作って、そのビュー経由で挿入操作を処理するトリガーを書いてみよう。

テーブルを作成：

CREATE TABLE courses (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    teacher_id INT NOT NULL
);

CREATE TABLE teachers (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

ビューを作成：

CREATE VIEW course_details AS
SELECT 
    courses.id AS course_id,
    courses.name AS course_name,
    teachers.name AS teacher_name
FROM courses
JOIN teachers ON courses.teacher_id = teachers.id;

問題：ビューは2つのテーブルをまとめてるから、普通に挿入できない。解決策：INSTEAD OFトリガーを使う。

トリガー用の関数を作成：

CREATE OR REPLACE FUNCTION insert_course_details()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO teachers (name) VALUES (NEW.teacher_name) RETURNING id INTO NEW.teacher_id;
    INSERT INTO courses (name, teacher_id) VALUES (NEW.course_name, NEW.teacher_id);
    RETURN NULL;  -- ビューにはデータを保存しない
END;
$$ LANGUAGE plpgsql;

トリガーを作成：

CREATE TRIGGER instead_of_insert_course_details
INSTEAD OF INSERT ON course_details
FOR EACH ROW
EXECUTE FUNCTION insert_course_details();

これでビューに直接データを挿入できるようになる：

INSERT INTO course_details (course_name, teacher_name)
VALUES ('マテマティカ', 'アレックス ミング');

SELECT * FROM courses;
-- 結果:
-- id | name        | teacher_id
--  1 | マテマティカ  | 1

SELECT * FROM teachers;
-- 結果:
-- id | name
--  1 | アレックス ミング

トリガータイプの比較

特徴と制限

BEFOREトリガーは、操作前にデータを変更できる。例えば名前を自動で大文字にしたりできるよ。

AFTERトリガーは、操作が終わった後なのでデータには影響できない。後処理専用だね。

INSTEAD OFトリガーはビュー専用。複数の関連テーブルにまたがる複雑な挿入・更新ロジックも実現できる。

今日はここまで！BEFORE、AFTER、INSTEAD OFが難しく感じても大丈夫。大事なのは基本の考え方と使いどころを覚えること。自分でいくつか例を作ってみて、手を動かしてみよう！