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