{"id":1032,"date":"2023-12-28T21:56:04","date_gmt":"2023-12-28T12:56:04","guid":{"rendered":"https:\/\/shinke1987.net\/?p=1032"},"modified":"2023-12-28T21:56:04","modified_gmt":"2023-12-28T12:56:04","slug":"postgresql%e3%81%a7%e3%83%88%e3%83%aa%e3%82%ac%e3%81%ae%e5%8b%95%e4%bd%9c%e7%a2%ba%e8%aa%8d","status":"publish","type":"post","link":"https:\/\/shinke1987.net\/?p=1032","title":{"rendered":"PostgreSQL\u3067\u30c8\u30ea\u30ac\u306e\u52d5\u4f5c\u78ba\u8a8d"},"content":{"rendered":"\n<h2 id=\"toc0\" class=\"wp-block-heading\">\u6982\u8981<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u30c8\u30ea\u30ac\u95a2\u6570\u306f\u30c8\u30ea\u30ac\u4f5c\u6210\u524d\u306b\u5b9a\u7fa9\u3057\u3066\u304a\u304f\u5fc5\u8981\u304c\u3042\u308b\u3002<\/li>\n\n\n\n<li>\u30c8\u30ea\u30ac\u95a2\u6570\u306f\u5f15\u6570\u3092\u53d6\u3089\u306a\u3044\u3001trigger\u578b\u3092\u8fd4\u3059\u95a2\u6570\u3068\u3057\u3066\u5ba3\u8a00\u3055\u308c\u308b\u5fc5\u8981\u304c\u3042\u308b\u3002<\/li>\n\n\n\n<li>\u540c\u4e00\u306e\u30c8\u30ea\u30ac\u95a2\u6570\u3092\u8907\u6570\u306e\u30c8\u30ea\u30ac\u306b\u7d10\u4ed8\u3051\u308b\u3053\u3068\u304c\u3067\u304d\u308b\u3002<\/li>\n\n\n\n<li>\u30c8\u30ea\u30ac\u4f5c\u6210\u6642\u306bWHEN\u53e5\u3092\u5229\u7528\u3059\u308b\u3053\u3068\u3067\u3001\u30c8\u30ea\u30ac\u3092\u767a\u52d5\u3055\u305b\u308b\u304b\u5236\u5fa1\u53ef\u80fd\u3002<\/li>\n<\/ul>\n\n\n\n<h2 id=\"toc1\" class=\"wp-block-heading\">\u30c7\u30fc\u30bf\u5909\u66f4\u306b\u3088\u308b\u30c8\u30ea\u30ac\u306e\u4f8b<\/h2>\n\n\n\n<p>\u53c2\u8003\uff1a<a href=\"https:\/\/www.postgresql.jp\/document\/14\/html\/plpgsql-trigger.html#PLPGSQL-DML-TRIGGER\">43.10.1 \u30c7\u30fc\u30bf\u5909\u66f4\u306b\u3088\u308b\u30c8\u30ea\u30ac<\/a><\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\n-- \u30c6\u30fc\u30d6\u30eb\u4f5c\u6210 ---------------------------------------\nCREATE TABLE trigger_data\n(\n    id   smallserial,\n    data varchar(100)\n);\nALTER TABLE trigger_data\n    RENAME COLUMN data TO temp_data;\n\n\nCREATE TABLE update_history\n(\n    id   smallserial,\n    data varchar(100),\n    date timestamp(0) without time zone\n);\nALTER TABLE update_history\n    RENAME COLUMN data TO temp_data;\nALTER TABLE update_history\n    RENAME COLUMN date to regist_date;\nALTER TABLE update_history\n    ADD COLUMN target_id smallint;\n\n\nCREATE TABLE insert_history\n(\n    id   smallserial,\n    data varchar(100),\n    date timestamp(0) without time zone\n);\nALTER TABLE insert_history\n    RENAME COLUMN data TO temp_data;\nALTER TABLE insert_history\n    RENAME COLUMN date TO regist_date;\n\n\nCREATE TABLE delete_history\n(\n    id   smallserial,\n    data varchar(100),\n    date timestamp(0) without time zone\n);\nALTER TABLE delete_history\n    RENAME COLUMN data TO temp_data;\nALTER TABLE delete_history\n    RENAME COLUMN date TO regist_date;\n\n\nCREATE TABLE truncate_history\n(\n    id   smallserial,\n    data varchar(100),\n    date timestamp(0) without time zone\n);\nALTER TABLE truncate_history\n    RENAME COLUMN data TO temp_data;\nALTER TABLE truncate_history\n    RENAME COLUMN date TO regist_date;\nALTER TABLE truncate_history\n    RENAME COLUMN temp_data TO table_name;\n\n\n-- \u30c8\u30ea\u30ac\u7528\u95a2\u6570\u4f5c\u6210 -------------------------------------------------\nCREATE OR REPLACE FUNCTION func_for_insert() RETURNS trigger AS\n$$\nBEGIN\n    INSERT INTO insert_history(temp_data, regist_date)\n    VALUES (NEW.temp_data, now());\n\n    RETURN NULL;\nEND;\n$$ LANGUAGE plpgsql;\n\n\nCREATE OR REPLACE FUNCTION func_for_update() RETURNS trigger AS\n$$\nBEGIN\n    INSERT INTO update_history(temp_data, regist_date, target_id)\n    VALUES (OLD.temp_data, now(), OLD.id);\n\n    -- \u672c\u95a2\u6570\u306fBEFORE\u3067\u5b9f\u884c\u3055\u308c\u308b\u306e\u3067\u3001NEW\u3092\u8fd4\u3055\u306a\u3044\u3068update\u6587\u306e\u5909\u66f4\u304c\u9069\u7528\u3055\u308c\u306a\u3044\u3002\n    RETURN NEW;\nEND;\n$$ LANGUAGE plpgsql;\n\n\nCREATE OR REPLACE FUNCTION func_for_delete() RETURNS trigger AS\n$$\nBEGIN\n    INSERT INTO delete_history(temp_data, regist_date)\n    VALUES (OLD.temp_data, now());\n\n    -- NEW\u3092\u6307\u5b9a\u3059\u308b\u3068\u524a\u9664\u3055\u308c\u306a\u3044\u3002\n    -- OLD\u3092\u8fd4\u3059\u3053\u3068\u3067\u524a\u9664\u5bfe\u8c61\u304c\u306f\u3063\u304d\u308a\u3059\u308b\u305f\u3081\u3068\u601d\u308f\u308c\u308b\u3002\n    RETURN OLD;\nEND;\n$$ LANGUAGE plpgsql;\n\n\nCREATE OR REPLACE FUNCTION func_for_truncate() RETURNS trigger AS\n$$\nBEGIN\n    INSERT INTO truncate_history(table_name, regist_date)\n    VALUES (TG_TABLE_SCHEMA || &#039;.&#039; || TG_TABLE_NAME, now());\n\n    RETURN OLD;\nEND;\n$$ LANGUAGE plpgsql;\n\n\n-- \u30c8\u30ea\u30ac\u4f5c\u6210\u3068\u30c7\u30fc\u30bf\u64cd\u4f5c ------------------------\n-- INSERT\nCREATE TRIGGER trg_for_insert\n    AFTER INSERT\n    ON trigger_data\n    FOR EACH ROW\nEXECUTE FUNCTION func_for_insert();\n\nINSERT INTO trigger_data(temp_data)\nVALUES (&#039;kakikukeko&#039;);\n\n\n-- UPDATE\nCREATE TRIGGER trg_for_update\n    BEFORE UPDATE\n    ON trigger_data\n    FOR EACH ROW\nEXECUTE FUNCTION func_for_update();\n\nUPDATE trigger_data\nSET temp_data = &#039;update\u3057\u307e\u3057\u305f&#039;\nWHERE id = 7;\n\n\n-- DELETE\nCREATE TRIGGER trg_for_delete\n    BEFORE DELETE\n    ON trigger_data\n    FOR EACH ROW\nEXECUTE FUNCTION func_for_delete();\n\nDELETE\nFROM trigger_data\nWHERE id = 4;\n\n\n-- TRUNCATE\nCREATE TRIGGER trg_for_truncate\n    BEFORE TRUNCATE\n    ON trigger_data\n    -- TRUNCATE\u306f\u884c\u3092\u6307\u5b9a\u3059\u308b\u3053\u3068\u304c\u306a\u3044\u306e\u3067\u3001FOR STATEMENT\u3092\u6307\u5b9a\u3059\u308b\u5fc5\u8981\u304c\u3042\u308b\u3002\n    FOR STATEMENT\nEXECUTE FUNCTION func_for_truncate();\n\nTRUNCATE trigger_data;\n<\/pre><\/div>","protected":false},"excerpt":{"rendered":"<p>\u6982\u8981 \u30c7\u30fc\u30bf\u5909\u66f4\u306b\u3088\u308b\u30c8\u30ea\u30ac\u306e\u4f8b \u53c2\u8003\uff1a43.10.1 \u30c7\u30fc\u30bf\u5909\u66f4\u306b\u3088\u308b\u30c8\u30ea\u30ac<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[56],"tags":[57],"class_list":["post-1032","post","type-post","status-publish","format-standard","hentry","category-postgresql","tag-postgresql"],"_links":{"self":[{"href":"https:\/\/shinke1987.net\/index.php?rest_route=\/wp\/v2\/posts\/1032","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/shinke1987.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/shinke1987.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/shinke1987.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/shinke1987.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1032"}],"version-history":[{"count":2,"href":"https:\/\/shinke1987.net\/index.php?rest_route=\/wp\/v2\/posts\/1032\/revisions"}],"predecessor-version":[{"id":1034,"href":"https:\/\/shinke1987.net\/index.php?rest_route=\/wp\/v2\/posts\/1032\/revisions\/1034"}],"wp:attachment":[{"href":"https:\/\/shinke1987.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1032"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/shinke1987.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1032"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/shinke1987.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1032"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}