トリガーとPL/pgSQL関数の連携：OLD、NEW、TG_OP

PostgreSQLのトリガーは、何かアクションがあった時に関数を実行できるだけじゃなくて、その関数に便利な変数も渡してくれるんだ。これらの変数のおかげで、テーブルのデータが操作前にどうだったか、操作後にどうなったか、どんな操作が行われたかが分かるようになってる。

• OLD — 操作前のテーブル行の古いデータが入ってる。UPDATEやDELETEトリガーで使うよ。INSERTの場合は「古い」ものがないから使えない。
• NEW — 操作後のテーブル行の新しいデータが入ってる。INSERTやUPDATEトリガーで使う。
• TG_OP — 今の操作が何か（INSERT、UPDATE、DELETE）をテキストで持ってる。

これらの変数は、トリガーに紐づいた関数の中で自動的に使えるよ。

理論だけじゃつまらないし、SQLにインデックスがないみたいに遅くて悲しいから、実践的な例で見てみよう！

OLDで古いデータにアクセスする

例えば、studentsテーブルがあるとするよ。誰かが学生の年齢を修正したとき（例えば「まだ20歳だよね」って勘違いした場合とか）を考えてみよう。

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

どんな変更があったか追跡したいなら、ログ用のテーブルを作ろう：

CREATE TABLE student_changes (
    change_id SERIAL PRIMARY KEY,
    student_id INT NOT NULL,
    old_value INT,
    new_value INT,
    change_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

次に、変更を記録する関数を作る。ここでOLDが役立つよ：

CREATE OR REPLACE FUNCTION log_student_changes()
RETURNS TRIGGER AS $$
BEGIN
    -- 年齢の変更をログに残す
    INSERT INTO student_changes (student_id, old_value, new_value)
    VALUES (OLD.id, OLD.age, NEW.age);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

そしてトリガーを作成：

CREATE TRIGGER student_age_update
AFTER UPDATE OF age ON students
FOR EACH ROW
WHEN (OLD.age IS DISTINCT FROM NEW.age) -- 年齢が変わった時だけ実行
EXECUTE FUNCTION log_student_changes();

学生を追加して、年齢を変更してみよう：

INSERT INTO students (name, age) VALUES ('アリサ', 20);

UPDATE students
SET age = 25
WHERE name = 'アリサ';

-- 変更ログを確認：
SELECT * FROM student_changes;

ログテーブルに「20」から「25」への年齢変更が記録されてるはず。魔法？いや、OLDのおかげ！

NEWで新しいデータを使う

今度は、新しい学生を追加した時に自動でIDと名前をログに残したいとしよう（ちょっとパラノイアだけど、たまには役立つよね）：

CREATE OR REPLACE FUNCTION log_new_student()
RETURNS TRIGGER AS $$
BEGIN
    -- 新しい学生のデータをログに残す
    INSERT INTO student_changes (student_id, old_value, new_value)
    VALUES (NEW.id, NULL, NEW.age); -- INSERTなので古い値はない
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER student_insert_log
AFTER INSERT ON students
FOR EACH ROW
EXECUTE FUNCTION log_new_student();

また新しい学生を追加して、ログを見てみよう：

INSERT INTO students (name, age) VALUES ('ボブ', 22);

-- ログを確認：
SELECT * FROM student_changes;

ログに新しい学生が追加されてるのが分かるはず。これでデータ管理も一段上だね！

TG_OPで操作タイプを判別する

でも、INSERTもUPDATEもDELETEも全部まとめてログに残したい場合はどうする？そんな時はTG_OPが便利！

汎用的な関数を作ろう：

CREATE OR REPLACE FUNCTION log_all_operations()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO student_changes (student_id, old_value, new_value)
        VALUES (NEW.id, NULL, NEW.age);

    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO student_changes (student_id, old_value, new_value)
        VALUES (OLD.id, OLD.age, NEW.age);

    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO student_changes (student_id, old_value, new_value)
        VALUES (OLD.id, OLD.age, NULL);

    END IF;

    RETURN NULL; -- AFTERトリガーのDELETEではNULLを返す
END;
$$ LANGUAGE plpgsql;

3つの操作すべてに反応するトリガーを作成：

CREATE TRIGGER universal_student_log
AFTER INSERT OR UPDATE OR DELETE ON students
FOR EACH ROW
EXECUTE FUNCTION log_all_operations();

学生を追加、変更、削除してみよう：

INSERT INTO students (name, age) VALUES ('チャーリー', 30);
UPDATE students SET age = 31 WHERE name = 'チャーリー';
DELETE FROM students WHERE name = 'チャーリー';

-- ログを確認：
SELECT * FROM student_changes;

すべての操作ログが見れるはず。1つのトリガーで全部管理できるって最高！

OLD、NEW、TG_OPを使う時のよくあるミス

トリガーを使ってると、よくあるトラブルにぶつかることがあるよ：

「なんでOLDがINSERTで使えないの？」 これはデフォルトの動作だよ。INSERTには古いデータがないから。NEWを使おう。

「DELETEでNEWが使えない時はどうする？」 これも想定通り。DELETEには新しいデータがないから。OLDを使おう。

トリガーのロジックが無限再帰を引き起こす。 トリガーが自分自身を呼び出さないように注意しよう。WHENブロックで条件をしっかり書いたり、TG_OPでチェックしたりするといいよ。

1

アンケート/クイズ

トリガー入門、レベル 57、レッスン 4

使用不可

トリガー入門

トリガー入門

オープン