{"id":549,"date":"2016-07-21T12:51:48","date_gmt":"2016-07-21T04:51:48","guid":{"rendered":"http:\/\/suherman.asia\/w2\/?p=549"},"modified":"2016-07-21T12:52:54","modified_gmt":"2016-07-21T04:52:54","slug":"create-trigger-postgresql","status":"publish","type":"post","link":"http:\/\/suherman.asia\/w2\/create-trigger-postgresql.html","title":{"rendered":"Create Trigger in PostgreSQL"},"content":{"rendered":"<p>Berikut ini adalah tutorial bagaimana membuat sebuah trigger di database postgre. trigger ini berfungsi menciptakan sebuah aksi yang ter pantik akibat ada aktivitas update, insert atau delete dalam sebuah database.<!--more--><\/p>\n<p>pertama kita coba siapkan dulu sebuah tabel yang akan kita pasang sebuah trigger<\/p>\n<pre>CREATE TABLE TitikObject(\r\n   id int4 serial primary key,\r\n   koordinat geometry(Point,4326),\r\n   longitude double precision NULL,\r\n   latitude double precision NULL\r\n);\r\n<\/pre>\n<p>Setelah itu kita siapkan dulu sebuah Function yang akan melakukan sebuah kegiatan ekstrak data geometry menjadi data numeric yang kemudian dimasukkan kedalam field longitude dan latitude. berikut ini adalah function<\/p>\n<pre>CREATE OR REPLACE FUNCTION public.extractgeom()\r\n  RETURNS trigger AS\r\n$BODY$\r\nBEGIN\r\nIF pg_trigger_depth() &lt;&gt; 1 THEN\r\n        RETURN NEW;\r\nEND IF;\r\n update public.titikobject set longitude=ST_X(koordinat), latitude= ST_Y(koordinat)\r\n where id=NEW.id;\r\n  RETURN NEW;\r\n  \r\nEND;\r\n$BODY$\r\n  LANGUAGE plpgsql VOLATILE SECURITY DEFINER\r\n<\/pre>\n<p>setelah function terbuat, selanjutnya kita siapkan sebuah trigger yang akan mengaktifkan function jika ada aktivitas insert atau update di tabel titikobject<\/p>\n<pre>CREATE TRIGGER tr_extractgeom\r\n  AFTER INSERT OR UPDATE ON public.titikobject\r\n    FOR EACH ROW \r\n    EXECUTE PROCEDURE public.extractgeom();\r\n<\/pre>\n<p>selanjutnya kita coba tes dengan memasukkan sebuah record ke tabel titikobject. pada saat proses insert ini saya hanya memasukkan nilai koordinatnya saja.<\/p>\n<pre>insert into public.titikobject (koordinat) \r\nvalues(ST_SetSRID(ST_MakePoint(-71.1043443253471, 42.3150676015829),4326));<\/pre>\n<p>terlihat pada query diatas saya hanya memasukkan 1 buah value saja ke field koordinat, namun karena sudah ada trigger maka otomatis value utk longitude dan latitude field seharusnya langsung terisi. mari kita lihat hasilnya<\/p>\n<pre>select * from public.titikobject<\/pre>\n<p>dan ternyata data geometry untuk field koordinat sudah ter ekstrak.<\/p>\n<p><a href=\"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2016\/07\/ekstrak.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-553\" src=\"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2016\/07\/ekstrak.jpg\" alt=\"ekstrak\" width=\"717\" height=\"223\" srcset=\"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2016\/07\/ekstrak.jpg 717w, http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2016\/07\/ekstrak-300x93.jpg 300w\" sizes=\"auto, (max-width: 717px) 100vw, 717px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p>demikian semoga bermanfaat.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Berikut ini adalah tutorial bagaimana membuat sebuah trigger di database postgre. trigger ini berfungsi menciptakan sebuah aksi yang ter pantik akibat ada aktivitas update, insert atau delete dalam sebuah database.<\/p>\n<p class=\"continue-reading-button\"> <a class=\"continue-reading-link\" href=\"http:\/\/suherman.asia\/w2\/create-trigger-postgresql.html\">Continue reading<i class=\"crycon-right-dir\"><\/i><\/a><\/p>\n","protected":false},"author":1,"featured_media":553,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[112,92,113,17],"class_list":["post-549","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-spatial","tag-postgis","tag-postgresql","tag-trigger","tag-tutorial"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v24.7 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Create Trigger in PostgreSQL - Suherman Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"http:\/\/suherman.asia\/w2\/create-trigger-postgresql.html\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Create Trigger in PostgreSQL - Suherman Blog\" \/>\n<meta property=\"og:description\" content=\"Berikut ini adalah tutorial bagaimana membuat sebuah trigger di database postgre. trigger ini berfungsi menciptakan sebuah aksi yang ter pantik akibat ada aktivitas update, insert atau delete dalam sebuah database. Continue reading\" \/>\n<meta property=\"og:url\" content=\"http:\/\/suherman.asia\/w2\/create-trigger-postgresql.html\" \/>\n<meta property=\"og:site_name\" content=\"Suherman Blog\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/emantin34\" \/>\n<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/emantin34\" \/>\n<meta property=\"article:published_time\" content=\"2016-07-21T04:51:48+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2016-07-21T04:52:54+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2016\/07\/ekstrak.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"717\" \/>\n\t<meta property=\"og:image:height\" content=\"223\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"admin\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"admin\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"http:\/\/suherman.asia\/w2\/create-trigger-postgresql.html#article\",\"isPartOf\":{\"@id\":\"http:\/\/suherman.asia\/w2\/create-trigger-postgresql.html\"},\"author\":{\"name\":\"admin\",\"@id\":\"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc\"},\"headline\":\"Create Trigger in PostgreSQL\",\"datePublished\":\"2016-07-21T04:51:48+00:00\",\"dateModified\":\"2016-07-21T04:52:54+00:00\",\"mainEntityOfPage\":{\"@id\":\"http:\/\/suherman.asia\/w2\/create-trigger-postgresql.html\"},\"wordCount\":167,\"commentCount\":0,\"publisher\":{\"@id\":\"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc\"},\"image\":{\"@id\":\"http:\/\/suherman.asia\/w2\/create-trigger-postgresql.html#primaryimage\"},\"thumbnailUrl\":\"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2016\/07\/ekstrak.jpg\",\"keywords\":[\"postgis\",\"PostgreSQL\",\"trigger\",\"Tutorial\"],\"articleSection\":[\"Spatial\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"http:\/\/suherman.asia\/w2\/create-trigger-postgresql.html#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"http:\/\/suherman.asia\/w2\/create-trigger-postgresql.html\",\"url\":\"http:\/\/suherman.asia\/w2\/create-trigger-postgresql.html\",\"name\":\"Create Trigger in PostgreSQL - Suherman Blog\",\"isPartOf\":{\"@id\":\"http:\/\/suherman.asia\/w2\/#website\"},\"primaryImageOfPage\":{\"@id\":\"http:\/\/suherman.asia\/w2\/create-trigger-postgresql.html#primaryimage\"},\"image\":{\"@id\":\"http:\/\/suherman.asia\/w2\/create-trigger-postgresql.html#primaryimage\"},\"thumbnailUrl\":\"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2016\/07\/ekstrak.jpg\",\"datePublished\":\"2016-07-21T04:51:48+00:00\",\"dateModified\":\"2016-07-21T04:52:54+00:00\",\"breadcrumb\":{\"@id\":\"http:\/\/suherman.asia\/w2\/create-trigger-postgresql.html#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"http:\/\/suherman.asia\/w2\/create-trigger-postgresql.html\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"http:\/\/suherman.asia\/w2\/create-trigger-postgresql.html#primaryimage\",\"url\":\"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2016\/07\/ekstrak.jpg\",\"contentUrl\":\"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2016\/07\/ekstrak.jpg\",\"width\":717,\"height\":223},{\"@type\":\"BreadcrumbList\",\"@id\":\"http:\/\/suherman.asia\/w2\/create-trigger-postgresql.html#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"http:\/\/suherman.asia\/w2\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Create Trigger in PostgreSQL\"}]},{\"@type\":\"WebSite\",\"@id\":\"http:\/\/suherman.asia\/w2\/#website\",\"url\":\"http:\/\/suherman.asia\/w2\/\",\"name\":\"Suherman Blog\",\"description\":\"Just Another Geek Site\",\"publisher\":{\"@id\":\"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"http:\/\/suherman.asia\/w2\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc\",\"name\":\"admin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"http:\/\/suherman.asia\/w2\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/eed6f889b4c2af2a8c18cb3bf63de6a4?s=96&d=retro&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/eed6f889b4c2af2a8c18cb3bf63de6a4?s=96&d=retro&r=g\",\"caption\":\"admin\"},\"logo\":{\"@id\":\"http:\/\/suherman.asia\/w2\/#\/schema\/person\/image\/\"},\"sameAs\":[\"http:\/\/suherman.asia\",\"https:\/\/www.facebook.com\/emantin34\",\"https:\/\/x.com\/emantin34\"],\"url\":\"http:\/\/suherman.asia\/w2\/author\/admin\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Create Trigger in PostgreSQL - Suherman Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"http:\/\/suherman.asia\/w2\/create-trigger-postgresql.html","og_locale":"en_US","og_type":"article","og_title":"Create Trigger in PostgreSQL - Suherman Blog","og_description":"Berikut ini adalah tutorial bagaimana membuat sebuah trigger di database postgre. trigger ini berfungsi menciptakan sebuah aksi yang ter pantik akibat ada aktivitas update, insert atau delete dalam sebuah database. Continue reading","og_url":"http:\/\/suherman.asia\/w2\/create-trigger-postgresql.html","og_site_name":"Suherman Blog","article_publisher":"https:\/\/www.facebook.com\/emantin34","article_author":"https:\/\/www.facebook.com\/emantin34","article_published_time":"2016-07-21T04:51:48+00:00","article_modified_time":"2016-07-21T04:52:54+00:00","og_image":[{"width":717,"height":223,"url":"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2016\/07\/ekstrak.jpg","type":"image\/jpeg"}],"author":"admin","twitter_misc":{"Written by":"admin","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"http:\/\/suherman.asia\/w2\/create-trigger-postgresql.html#article","isPartOf":{"@id":"http:\/\/suherman.asia\/w2\/create-trigger-postgresql.html"},"author":{"name":"admin","@id":"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc"},"headline":"Create Trigger in PostgreSQL","datePublished":"2016-07-21T04:51:48+00:00","dateModified":"2016-07-21T04:52:54+00:00","mainEntityOfPage":{"@id":"http:\/\/suherman.asia\/w2\/create-trigger-postgresql.html"},"wordCount":167,"commentCount":0,"publisher":{"@id":"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc"},"image":{"@id":"http:\/\/suherman.asia\/w2\/create-trigger-postgresql.html#primaryimage"},"thumbnailUrl":"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2016\/07\/ekstrak.jpg","keywords":["postgis","PostgreSQL","trigger","Tutorial"],"articleSection":["Spatial"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["http:\/\/suherman.asia\/w2\/create-trigger-postgresql.html#respond"]}]},{"@type":"WebPage","@id":"http:\/\/suherman.asia\/w2\/create-trigger-postgresql.html","url":"http:\/\/suherman.asia\/w2\/create-trigger-postgresql.html","name":"Create Trigger in PostgreSQL - Suherman Blog","isPartOf":{"@id":"http:\/\/suherman.asia\/w2\/#website"},"primaryImageOfPage":{"@id":"http:\/\/suherman.asia\/w2\/create-trigger-postgresql.html#primaryimage"},"image":{"@id":"http:\/\/suherman.asia\/w2\/create-trigger-postgresql.html#primaryimage"},"thumbnailUrl":"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2016\/07\/ekstrak.jpg","datePublished":"2016-07-21T04:51:48+00:00","dateModified":"2016-07-21T04:52:54+00:00","breadcrumb":{"@id":"http:\/\/suherman.asia\/w2\/create-trigger-postgresql.html#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["http:\/\/suherman.asia\/w2\/create-trigger-postgresql.html"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"http:\/\/suherman.asia\/w2\/create-trigger-postgresql.html#primaryimage","url":"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2016\/07\/ekstrak.jpg","contentUrl":"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2016\/07\/ekstrak.jpg","width":717,"height":223},{"@type":"BreadcrumbList","@id":"http:\/\/suherman.asia\/w2\/create-trigger-postgresql.html#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"http:\/\/suherman.asia\/w2"},{"@type":"ListItem","position":2,"name":"Create Trigger in PostgreSQL"}]},{"@type":"WebSite","@id":"http:\/\/suherman.asia\/w2\/#website","url":"http:\/\/suherman.asia\/w2\/","name":"Suherman Blog","description":"Just Another Geek Site","publisher":{"@id":"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"http:\/\/suherman.asia\/w2\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc","name":"admin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"http:\/\/suherman.asia\/w2\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/eed6f889b4c2af2a8c18cb3bf63de6a4?s=96&d=retro&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/eed6f889b4c2af2a8c18cb3bf63de6a4?s=96&d=retro&r=g","caption":"admin"},"logo":{"@id":"http:\/\/suherman.asia\/w2\/#\/schema\/person\/image\/"},"sameAs":["http:\/\/suherman.asia","https:\/\/www.facebook.com\/emantin34","https:\/\/x.com\/emantin34"],"url":"http:\/\/suherman.asia\/w2\/author\/admin"}]}},"_links":{"self":[{"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/posts\/549","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/comments?post=549"}],"version-history":[{"count":5,"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/posts\/549\/revisions"}],"predecessor-version":[{"id":555,"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/posts\/549\/revisions\/555"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/media\/553"}],"wp:attachment":[{"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/media?parent=549"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/categories?post=549"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/tags?post=549"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}