🕵️
PostgreSQLのトリガーで簡素な監査用カラム(created_at等)を自動記録する
テーブルを設計するとき、created_at / updated_at や created_by / updated_by のような監査用カラムをとりあえず用意することはよくあります。これらはpgAuditなどと比べると簡素なものですが、監査目的のカラムである以上、アプリケーション側で直接操作するのではなく、データベース側で自動的に管理するのが理想的です[1]。PostgreSQLではこのような処理をトリガーを使って簡単に実装できます。以下、その方法を紹介します。
まず、NOT NULLを付与するため、各カラムにはDEFAULTを設定します。
CREATE TABLE your_table (
id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
created_by TEXT NOT NULL DEFAULT CURRENT_USER,
updated_by TEXT NOT NULL DEFAULT CURRENT_USER
);
次にトリガーに利用する関数ですが、以下のように定義します。INSERT/UPDATEの分岐は TG_OP で行います。UPDATEの場合にcreated_atとcreated_byはOLD.<カラム名>で更新前の値を参照してその値を設定することで値が上書きされるのを防いでいます。
CREATE OR REPLACE FUNCTION set_audit_columns()
RETURNS trigger AS $$
BEGIN
IF TG_OP = 'INSERT' THEN
NEW.created_at := NOW();
NEW.updated_at := NOW();
NEW.created_by := CURRENT_USER;
NEW.updated_by := CURRENT_USER;
ELSIF TG_OP = 'UPDATE' THEN
NEW.created_at := OLD.created_at;
NEW.updated_at := NOW();
NEW.created_by := OLD.created_by;
NEW.updated_by := CURRENT_USER;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
最後にこれをINSERT/UPDATE時のトリガーとして登録して完了です。
CREATE TRIGGER trigger_set_audit_columns
BEFORE INSERT OR UPDATE ON your_table
FOR EACH ROW
EXECUTE FUNCTION set_audit_columns();
なお、CURRENT_USERはSQLを実行しているDBユーザーですが、アプリケーション側でset_configを利用して値を設定し、set_audit_columnsのCURRENT_USERの部分でcurrent_settingを呼び出すことでもう少し粒度の細かいユーザ追跡が可能です[2]。ただ、ユーザ用アプリケーション、管理者用アプリケーション、各運用作業者でDBユーザーを適切に分けていれば、簡易的な監査用途であれば十分に実用的です。
Discussion