レコードが変更されたときに last_modifiedフィールドを自動更新するトリガー

例えば、学生とコースを管理するアプリを作ってて、studentsテーブルがあるとする。このテーブルにはlast_modifiedフィールドがあって、レコードのデータ（例えば学生の名前や年齢）が変更されるたびに自動で更新されてほしいんだ。

毎回SQLクエリでlast_modifiedを手動で更新する代わりに、トリガーを作って自動でやってもらおう！

studentsテーブルの構造

まずは例で使うstudentsテーブルを作ってみよう。このテーブルは学生の基本情報を持ってるよ：

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY, -- 学生のユニークID
    name VARCHAR(100) NOT NULL,   -- 学生の名前
    age INT,                      -- 学生の年齢
    last_modified TIMESTAMP NOT NULL DEFAULT NOW() -- 最終更新日時
);

• last_modifiedフィールドは、レコード作成時にデフォルトで現在時刻（NOW()）が入るよ。
• このフィールドは学生データが変更されるたびに自動で更新される。

テスト用のデータをテーブルに入れてみよう：

INSERT INTO students (name, age)
VALUES 
    ('オットー・リン', 20),
    ('マリア・チー', 22),
    ('アレックス・ソング', 19);

今、テーブルのデータはこんな感じ：

last_modifiedを更新する関数の作成

PL/pgSQLの関数をトリガーから呼び出して、last_modifiedフィールドの値を更新するよ。この関数はレコード変更の直前に自動で呼ばれる。

update_last_modified関数を作ろう：

CREATE OR REPLACE FUNCTION update_last_modified()
RETURNS TRIGGER AS $$
BEGIN
    -- last_modifiedフィールドを現在時刻に更新
    NEW.last_modified := NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

• NEWは、変更後のレコードデータが入ってる特別な変数だよ。
• NEW.last_modifiedにNOW()（今の日時）をセットしてる。
• 関数は更新済みのNEWを返す。これがトリガーの正しい動作に必要なんだ。

トリガーの作成

次に、studentsテーブルのレコードが更新されるたびにupdate_last_modified関数を自動で呼ぶトリガーを作るよ。

CREATE TRIGGER set_last_modified
BEFORE UPDATE ON students
FOR EACH ROW
EXECUTE FUNCTION update_last_modified();

ここでやってること：

• BEFORE UPDATEは、更新操作の前にトリガーが動くって意味。
• FOR EACH ROWは、変更される各行ごとにトリガーが動く。
• EXECUTE FUNCTION update_last_modified()でupdate_last_modified関数を呼び出してる。

トリガーのテスト

じゃあ、トリガーがちゃんと動くか確認してみよう。まずはstudentsテーブルの今のデータを見てみる：

SELECT * FROM students;

結果：

次に、student_id = 1の学生の年齢を更新してみる：

UPDATE students
SET age = 21
WHERE student_id = 1;

もう一度テーブルのデータを見てみよう：

SELECT * FROM students;

期待される結果：

注目してね：student_id = 1のレコードだけlast_modifiedが今の時刻に更新されてて、他のレコードはそのままだよ。

トリガーロジックの拡張

例えば、今度はlast_modifiedフィールドを特定のカラムが変更されたときだけ更新したいとしよう。例えば、学生の名前や年齢が変わったときだけトリガーを動かして、それ以外の変更では動かしたくない場合ね。

そのためには、トリガー定義にWHEN句を追加できるよ。

条件付きの新しいトリガーを作ってみよう：

DROP TRIGGER IF EXISTS set_last_modified ON students;

CREATE TRIGGER set_last_modified
BEFORE UPDATE ON students
FOR EACH ROW
WHEN (OLD.name IS DISTINCT FROM NEW.name OR OLD.age IS DISTINCT FROM NEW.age)
EXECUTE FUNCTION update_last_modified();

ここでは：

• WHEN条件で、nameやageカラムの古い値（OLD）と新しい値（NEW）が違うかどうかをチェックしてる。
• どちらのカラムも変わってなければ、トリガーは動かない。

もう一度テーブルのデータを更新して、新しいロジックをテストしてみよう。

トリガー利用のおすすめポイント

1. トリガーの使いすぎに注意！便利だけど、DBのロジックが複雑になったり、デバッグが大変になったりするからね。
2. トリガーが何をしてるか、どんな場合に使うのか、ちゃんとドキュメントを書こう。
3. WHEN条件を使って、意図しないトリガー発動を減らそう。
4. トリガーはDBのパフォーマンスに影響することもあるから、特にレコード数が多いテーブルでは注意しよう。

トリガーでよくあるミス

データの間違った変更。例えば、NEWに値をセットし忘れて、元のデータをそのまま返しちゃうとか。

条件のミス。例えば、WHEN条件を付け忘れて、必要ないときまでトリガーが動いちゃうとか。

再帰。トリガーが関数を呼んで、その関数がまたトリガーを呼ぶ…みたいな無限ループを作っちゃうことも。PostgreSQLには再帰防止の仕組みがあるけど、こういう状況は避けた方がいいよ。

この例みたいに、トリガーを使うとデータの自動更新がすごく楽になる。実際のプロジェクトでも、変更履歴の記録やデータ整合性の維持、ルーチン作業の自動化なんかによく使われてるよ。