PostgreSQLのTRIGGER備忘録
- 2020/10/20
- aikawa
PostgreSQLのTRIGGERは滅多に使わない＞忘れてしまっていたので思い出すのを兼ねて備忘録とします。
今回はテーブルに対しての追加更新削除時のデータを、ログテーブルに丸ごと保存する例です。
まずpostgres等のスーパーユーザでCREATE LANGUAGE。
CREATE LANGUAGE plpgsql;
SELECT * FROM pg_language WHERE lanname = 'plpgsql'; --確認
以降はスーパーユーザーの必要は無いので、データベースオーナーが実施。
テスト用テーブルの作成。
CREATE TABLE public.tb(
id serial NOT NULL,
note text,
create_time timestamp without time zone NOT NULL DEFAULT now(),
CONSTRAINT tb_pkey PRIMARY KEY (id)
);CREATE SCHEMA logs;
CREATE TABLE logs.log_tb AS
SELECT 0::bigint AS log_id,''::text AS oper,null::timestamp without time zone AS copy_time,*
FROM public.tb WHERE id = 0;
CREATE SEQUENCE logs.log_tb_log_id_seq;
ALTER TABLE logs.log_tb ADD PRIMARY KEY (log_id);CREATE OR REPLACE FUNCTION public.tb_copy() RETURNS trigger AS
$BODY$
BEGIN
IF (TG_OP = 'DELETE') THEN
INSERT INTO logs.log_tb SELECT nextval('logs.log_tb_log_id_seq'::regclass),TG_OP,now(),* FROM public.tb WHERE id = OLD.id;
RETURN OLD;
ELSE
INSERT INTO logs.log_tb SELECT nextval('logs.log_tb_log_id_seq'::regclass),TG_OP,now(),* FROM public.tb WHERE id = NEW.id;
RETURN NEW;
END IF;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;CREATE TRIGGER tb_change AFTER INSERT OR UPDATE ON public.tb FOR EACH ROW EXECUTE PROCEDURE public.tb_copy();
CREATE TRIGGER tb_delete BEFORE DELETE ON public.tb FOR EACH ROW EXECUTE PROCEDURE public.tb_copy();
最後に、データを操作してログが取れるかを確認。
INSERT INTO public.tb(note) VALUES('TEST');
INSERT INTO public.tb(note) VALUES('TEST2');
UPDATE public.tb SET note = 'UPDATE TEST';
DELETE FROM public.tb;
SELECT * FROM logs.log_tb;※log_idやidが増加してるのは、何度か試したので。
※時間が一緒なのは一括実行の結果なので、一行ずつ実行すれば異なる。
