トリガーを使って変更履歴の差分を残す

はじめに

「データが登録もしくは更新される度に、履歴テーブルに変更されたカラム情報を残したい。」という要件の作業を担当することになりました。履歴データ作成は、トリガー利用をして来ました。今回もプログラムよりもトリガーで実現する方が簡単な気がしました。実際、やってみると簡単でした。その方法を以下にまとめます。

初期化

初回は実行不要ですが、検証のために何度か作り直しをしたのでDDLを残しておきます。

DROP TRIGGER IF EXISTS trigger_sample_change_tracking ON delivery.sample;
DROP FUNCTION IF EXISTS fnc_sample_change_tracking();
DROP TABLE IF EXISTS delivery.sample_change_tracking;
DROP TABLE IF EXISTS delivery.sample;

サンプルテーブルの作成

CREATE TABLE delivery.sample (
  sample_id bigserial NOT NULL,
  status int DEFAULT 100 NOT NULL,
  col01 text,
  col02 text,
  col03 text,
  create_date timestamp with time zone DEFAULT now() NOT NULL,
  update_date timestamp with time zone DEFAULT now() NOT NULL,
  PRIMARY KEY(sample_id)
);

サンプルの履歴テーブルの作成

CREATE TABLE delivery.sample_change_tracking (
  sample_change_tracking bigserial NOT NULL,
  status int DEFAULT 100 NOT NULL,
  changed_on timestamp with time zone DEFAULT now() NOT NULL,
  sample_id bigint NOT NULL REFERENCES delivery.sample(sample_id),
  change_flag_col01 boolean,
  change_flag_col02 boolean,
  change_flag_col03 boolean,
  create_date timestamp with time zone DEFAULT now() NOT NULL,
  update_date timestamp with time zone DEFAULT now() NOT NULL,
  remarks text,
  PRIMARY KEY(sample_change_tracking)
);

トリガープロシージャの作成

CREATE OR REPLACE FUNCTION fnc_sample_change_tracking() RETURNS TRIGGER AS $$
DECLARE
  sample_change_tracking_id bigint;
BEGIN
  IF (TG_OP = 'UPDATE') THEN
    INSERT INTO delivery.sample_change_tracking (
      sample_id,
      status,
      changed_on,
      change_flag_col01,
      change_flag_col02,
      change_flag_col03,
      create_date,
      update_date,
      remarks
    ) VALUES (
      NEW.sample_id,
      NEW.status,
      now(),
      CASE WHEN concat('', NEW.col01) != concat('', OLD.col01) THEN true ELSE false END,
      CASE WHEN concat('', NEW.col02) != concat('', OLD.col02) THEN true ELSE false END,
      CASE WHEN concat('', NEW.col03) != concat('', OLD.col03) THEN true ELSE false END,
      NEW.create_date,
      NEW.update_date,
      CASE
        WHEN NEW.status = 100 AND (concat('', NEW.col01) != concat('', OLD.col01) OR concat('', NEW.col02) != concat('', OLD.col02)) THEN 'API１'
        WHEN NEW.status = 100 AND (concat('', NEW.col03) != concat('', OLD.col03)) THEN  'API２'
        WHEN NEW.status = 100 THEN '宛先取り込み完了'
      END
    );
  ELSE
    INSERT INTO delivery.sample_change_tracking (
      sample_id,
      changed_on,
      change_flag_col01,
      change_flag_col02,
      change_flag_col03,
      create_date,
      update_date,
      remarks
    ) VALUES (
      NEW.sample_id,
      now(),
      true,
      true,
      true,
      NEW.create_date,
      NEW.update_date,
      '新規作成'
    );
  END IF;
  RETURN NULL;
END
$$ LANGUAGE plpgsql;

トリガーの作成

CREATE TRIGGER trigger_sample_change_tracking
AFTER INSERT OR UPDATE ON delivery.sample
FOR EACH ROW EXECUTE PROCEDURE fnc_sample_change_tracking();

検証用SQL

SELECT * FROM delivery.sample WHERE sample_id = 5;
SELECT * FROM delivery.sample_change_tracking WHERE sample_id = 5;

INSERT INTO delivery.sample(status, col01, col02, col03) VALUES (100, 'a', 'b', 'c');
UPDATE delivery.sample SET status = 100, col02 = 'B', update_date = NOW() WHERE sample_id = 5;
UPDATE delivery.sample SET status = 101, update_date = NOW() WHERE sample_id = 5;
UPDATE delivery.sample SET status = 100, col03 = 'C', update_date = NOW() WHERE sample_id = 5;

References

• PostgreSQL 9.2.4文書 CREATE TRIGGER
• PostgreSQL 9.2.4文書 39.9. トリガプロシージャ
• PostgreSQL 9.3.2文書 40.9. トリガプロシージャ
• ERROR: record “old” is not assigned yet