1
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

消えないアイツ。(DELETEしてもレコードが消えない)

Posted at

さてみなさん。
以下のトリガー関数のバグとその動作を指摘ください。(関数の直後で回答しますが)

環境 | 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;

脱力するほどくだらないミスですが、気づけば一瞬だけど、知らないとハマるので。

1
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
1
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?