さてみなさん。
以下のトリガー関数のバグとその動作を指摘ください。(関数の直後で回答しますが)
環境 | PostgreSQL 9.4
iketenai.sql
CREATE OR REPLACE FUNCTION update_test_data_changelog() RETURNS trigger AS $BODY$
BEGIN
IF TG_OP = 'DELETE' THEN
-- 削除時はIDを削除データに追加する
INSERT INTO change_log (edit_id, data, deleted) VALUES (OLD.id, OLD.data, TRUE);
ELSEIF TG_OP = 'UPDATE' THEN
INSERT INTO change_log (edit_id, data) VALUES (NEW.id, NEW.data);
ELSE
INSERT INTO change_log (edit_id, data) VALUES (NEW.id, NEW.data);
END IF;
RETURN NEW;
END;
$BODY$ LANGUAGE plpgsql;
こたえ
DELETEしてもデータが消えない。(呪いか)
原因は 最後のRETRUN NEW;
削除したのに、RETURN NEWなんてしてるからおかしな挙動になるわけで。
一通り再現するなら以下のSQLを実行してみるといい。
iketenai.sql
create table test_data (
id serial not null primary key
, data text not null
);
create table change_log (
id serial not null primary key
, edit_id int not null
, data text not null
, deleted BOOL NOT NULL DEFAULT FALSE
, edit_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);
CREATE OR REPLACE FUNCTION update_test_data_changelog() RETURNS trigger AS $BODY$
BEGIN
IF TG_OP = 'DELETE' THEN
-- 削除時はIDを削除データに追加する
INSERT INTO change_log (edit_id, data, deleted) VALUES (OLD.id, OLD.data, TRUE);
-- RETURN OLD;
ELSEIF TG_OP = 'UPDATE' THEN
INSERT INTO change_log (edit_id, data) VALUES (NEW.id, NEW.data);
-- RETURN NEW;
ELSE
INSERT INTO change_log (edit_id, data) VALUES (NEW.id, NEW.data);
-- RETURN NEW;
END IF;
RETURN NEW;
END;
$BODY$ LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS update_test_data_changelog_trigger ON test_data;
CREATE TRIGGER update_test_data_changelog_trigger
BEFORE INSERT OR UPDATE OR DELETE ON test_data
FOR EACH ROW EXECUTE PROCEDURE update_test_data_changelog();
INSERT INTO test_data (data) VALUES ('data1');
INSERT INTO test_data (data) VALUES ('data2');
INSERT INTO test_data (data) VALUES ('data3');
INSERT INTO test_data (data) VALUES ('data4');
UPDATE test_data SET data = 'data1-1' WHERE id = 1;
DELETE FROM test_data WHERE id = 4;
data4が消えない。
で、修正は以下の通り、INSERT / UPDATEはRETURN NEW; DELETEは RETURN OLD;
で解決。
imasara.sql
CREATE OR REPLACE FUNCTION update_test_data_changelog() RETURNS trigger AS $BODY$
BEGIN
IF TG_OP = 'DELETE' THEN
-- 削除時はIDを削除データに追加する
INSERT INTO change_log (edit_id, data, deleted) VALUES (OLD.id, OLD.data, TRUE);
RETURN OLD;
ELSEIF TG_OP = 'UPDATE' THEN
INSERT INTO change_log (edit_id, data) VALUES (NEW.id, NEW.data);
RETURN NEW;
ELSE
INSERT INTO change_log (edit_id, data) VALUES (NEW.id, NEW.data);
RETURN NEW;
END IF;
END;
$BODY$ LANGUAGE plpgsql;
脱力するほどくだらないミスですが、気づけば一瞬だけど、知らないとハマるので。