はじめに
ユーザーからとあるシステムの実績を登録するテーブルに回数を保持する列があり数値型3桁になっているが、これを数値型1桁にしたいという依頼があった。システム上10回を超えるのは作業者が間違って操作している可能性があるとのこと。
データを調べると過去にも10回を超えるデータはそれなりに存在しており、数値型を1桁にするとなると現在のテーブルのデータはバックアップして、データを初期化することになる。
全国で同じ形式で統一してやっているのに1拠点だけ桁数を変更するのは管理的にどうかと思い、トリガーを使えばチェックできるのではないかと調査した。
調査
もともとレプリケーション用にトリガーを作成しており、これを一部変更して登録時に回数の値をチェックして例外エラーにすればいい。
INSERTトリガーで新しい値の回数をチェックして10回以上なら例外エラーにする。試してみるとあっさり出来たので上長に報告すると下記の返答がきた。
レプリケーション用のトリガーは作成ツールの仕様を気にしないといけないので、別のトリガーを付ける対応にしたいですが、不都合ありますか？
PostgreSQLって複数トリガー可能だったっけ？
同一イベントに同じ種類の複数のトリガが定義された場合、名前のアルファベット順で実行されます。
https://www.postgresql.jp/document/9.6/html/sql-createtrigger.html
複数トリガー可能でした、ただ名前だけは気をつける必要があります。
すでに付いているレプリケーション用のトリガーより前に実行されるような名前で作成します。
対応
testテーブルを例とします。例外エラーは埋め込みです。
CREATE OR REPLACE FUNCTION trg_fnc_check_test() RETURNS trigger AS
$BODY$
DECLARE
BEGIN
IF (TG_OP = 'INSERT') THEN
IF NEW."no" > 9 THEN
RAISE EXCEPTION '回数オーバー ';
END IF;
RETURN NULL;
END IF;
END;
$BODY$
LANGUAGE 'plpgsql'
CREATE TRIGGER trg_check_test
AFTER INSERT ON test FOR EACH ROW
EXECUTE PROCEDURE trg_fnc_check_test()
-- トリガーの無効
alter table RESH003050 disable trigger trg_check_test
-- トリガーの有効
alter table RESH003050 enable trigger trg_check_test
-- トリガーの有効/無効の確認 「D」が無効、「O」が有効
SELECT * FROM pg_trigger
WHERE tgrelid = 'test'::regclass
最後に
テーブルの数値型を1桁にすると10回のみしかチェックできませんが、このトリガー方式にすればチェックする回数を変更することができるわけです。
ユーザーの言われるまま受け入れるわけではなく、よりよい提案をするのがお仕事です。