{"id":16231,"date":"2021-04-22T21:14:43","date_gmt":"2021-04-22T19:14:43","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/pass-a-variable-to-a-trigger-in-postgresql\/"},"modified":"2021-04-22T21:14:43","modified_gmt":"2021-04-22T19:14:43","slug":"pass-a-variable-to-a-trigger-in-postgresql","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/pass-a-variable-to-a-trigger-in-postgresql\/","title":{"rendered":"Pass a variable to a trigger in PostgreSQL"},"content":{"rendered":"<h2>By Franck Pachot<\/h2>\n<p>.<br \/>\nWith Oracle there are many ways to set a state in the session: context variables, set with DBMS_SESSION.SET_CONTEXT and retrieved with SYS_CONTEXT, or package global variables, or global or private temporary tables with ON COMMIT PRESERVE ROWS,&#8230;<\/p>\n<p>How would you do it in PostgreSQL? I&#8217;m taking an example from a <a href=\"https:\/\/www.yugabyte.com\/slack\">Yugabyte slack<\/a> question, as Yugabyte SQL layer is the PostgreSQL one.<\/p>\n<pre><code>\nCREATE TABLE employees (\n  employee_no integer PRIMARY KEY,\n  name text,\n  department text\n);\n\nINSERT INTO employees (employee_no, name, department) VALUES \n(1221, 'John Smith', 'Marketing'),\n(1222, 'Bette Davis', 'Sales'),\n(1223, 'Lucille Ball', 'Operations'),\n(1224, 'John Zimmerman', 'Sales');\n\nCREATE TABLE mgr_table (\n  mgr_id integer references employees,\n  employee_no integer references employees,\n  primary key(mgr_id, employee_no)\n);\n\ninsert into mgr_table values(1223,1222);\n<\/code><\/pre>\n<p>We have an employee\/manager table example:<\/p>\n<pre><code>\nCREATE TABLE employee_dept_changes (\n  employee_no integer NOT NULL references employees,\n  name text,\n  department text,\n  changed_on TIMESTAMP(6) NOT NULL,\n  changed_by integer\n);\n<\/code><\/pre>\n<p>This table is there to log the changes when an employee is transferred to another manager. You can see that we log the values, but also the context about who is transferring because this operation is allowed only by a manager.<\/p>\n<p>Ideally, all database operations are encapsulated in a microservice. The application calls a stored procedure that does all the logic: update the manager and log the change. And this procedure has all context. However, the idea here is to do this logging through a trigger. The trigger has access to the table values, but how can we pass the context of who is doing this change? Without context variables, or package variables, this requires something else.<\/p>\n<p>If you look at the stackoverflow for <a href=\"https:\/\/stackoverflow.com\/questions\/66972555\/is-there-a-way-to-pass-a-variable-as-an-argument-to-a-trigger-function-in-yugaby\" target=\"_blank\" rel=\"noopener\">this question<\/a>, you will see Frits Hoogland and Bryn Llewellyn answers with workaround and recommendations. I&#8217;m just adding here a possibility (with the same recommendation: don&#8217;t have procedures calling SQL calling triggers, but all logic encapsulated in procedural code calling SQL).<\/p>\n<pre><code>\nALTER TABLE employees ADD COLUMN\n  \"_trigger_state_mgr_id\" integer --&gt; this is added to pass the state variable\n<\/code><\/pre>\n<p>I&#8217;m adding a column to my table. This will not take any storage but will be used only to pass some values to the trigger<\/p>\n<pre><code>\nCREATE OR REPLACE FUNCTION record_dept_changes()\nRETURNS TRIGGER AS\n$$\nBEGIN\n IF NEW.department  OLD.department\n  THEN INSERT INTO employee_dept_changes(employee_no, name, department, changed_on, changed_by)\n   VALUES(OLD.employee_no, OLD.name, OLD.department, now(), NEW.\"_trigger_state_mgr_id\"  --&gt; passing the state to the added column\n   );\n END IF;\n NEW.\"_trigger_state_mgr_id\" := null; --&gt; setting null not to persist anything (or maybe a real \"last_update_by\" column makes sense?)\n RETURN NEW;\nEND;\n$$\nLANGUAGE 'plpgsql';\n<\/code><\/pre>\n<p>The trigger logs the change into the &#8220;changes&#8221; table, reading this added column value. But I set this value to null once used because I don&#8217;t want to waste any storage (disk and memory) for it.<\/p>\n<pre><code>\nCREATE TRIGGER dept_changes\nBEFORE UPDATE ON employees\nFOR EACH ROW\nEXECUTE PROCEDURE record_dept_changes();\n<\/code><\/pre>\n<p>In PostgreSQL the trigger calls the procedure which has access to the NEW. valies<\/p>\n<pre><code>\nCREATE OR REPLACE PROCEDURE transfer_employee(integer, integer, text)\nLANGUAGE plpgsql\nAS $$\nBEGIN\n  -- IF employee reports to mgr, allow mgr to transfer person\n  IF EXISTS (SELECT employee_no FROM mgr_table where mgr_id = $1 and employee_no = $2)\n  THEN\n   UPDATE employees\n   SET department = $3, \"_trigger_state_mgr_id\" = $1 --&gt; passing the state through the column\n   WHERE employee_no = $2;\n   COMMIT;\n  END IF;\nEND;\n$$;\n<\/code><\/pre>\n<p>Here is my procedure that implements the transfer service. In addition to checking if the manager is allowed to do the transfer, and doing the update, I set the additional column with the context value.<\/p>\n<p>This column is declared in the metadata, is used by the update statement but is discarded by the trigger. If PostgreSQL had a feature like the Oracle invisible column, I would set this column as invisible as I don&#8217;t want to see it in SELECT * or INSERT to it. I can do the same with a view on top of the table.<\/p>\n<p>In summary:<\/p>\n<ul>\n<li>you may have less feature in open source software than commercial ones, but this probably means that there&#8217;s something to do differently in the application design. Commercial vendors can implement anything when important customers pay for it. An open source community will not add more complexity to workaround a problem that should be addressed by the application design. Don&#8217;t call SQL DML with trigger magic behind. Call a procedure that has all the logic, including all DML (main change and audit) and verifications<\/li>\n<li>workarounds are valid as long as you document them (what they do, and why) and verify all side effects (like setting it to null afterwards). There are often many workarounds and choose the one that makes more sense. Here, maybe the &#8220;_trigger_state_mgr_id&#8221; will become a real column to persist one day, if there&#8217;s a need to store the latest manager that assigned the employee to a department<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>By Franck Pachot . With Oracle there are many ways to set a state in the session: context variables, set with DBMS_SESSION.SET_CONTEXT and retrieved with SYS_CONTEXT, or package global variables, or global or private temporary tables with ON COMMIT PRESERVE ROWS,&#8230; How would you do it in PostgreSQL? I&#8217;m taking an example from a Yugabyte [&hellip;]<\/p>\n","protected":false},"author":28,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[83],"tags":[2241,77,2215],"type_dbi":[],"class_list":["post-16231","post","type-post","status-publish","format-standard","hentry","category-postgresql","tag-parameter","tag-postgresql","tag-trigger"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.5) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>Pass a variable to a trigger in PostgreSQL - dbi 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=\"https:\/\/www.dbi-services.com\/blog\/pass-a-variable-to-a-trigger-in-postgresql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Pass a variable to a trigger in PostgreSQL\" \/>\n<meta property=\"og:description\" content=\"By Franck Pachot . With Oracle there are many ways to set a state in the session: context variables, set with DBMS_SESSION.SET_CONTEXT and retrieved with SYS_CONTEXT, or package global variables, or global or private temporary tables with ON COMMIT PRESERVE ROWS,&#8230; How would you do it in PostgreSQL? I&#8217;m taking an example from a Yugabyte [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/pass-a-variable-to-a-trigger-in-postgresql\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2021-04-22T19:14:43+00:00\" \/>\n<meta name=\"author\" content=\"Open source Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Open source Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/pass-a-variable-to-a-trigger-in-postgresql\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/pass-a-variable-to-a-trigger-in-postgresql\\\/\"},\"author\":{\"name\":\"Open source Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/59554f0d99383431eb6ed427e338952b\"},\"headline\":\"Pass a variable to a trigger in PostgreSQL\",\"datePublished\":\"2021-04-22T19:14:43+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/pass-a-variable-to-a-trigger-in-postgresql\\\/\"},\"wordCount\":584,\"commentCount\":0,\"keywords\":[\"parameter\",\"PostgreSQL\",\"Trigger\"],\"articleSection\":[\"PostgreSQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/pass-a-variable-to-a-trigger-in-postgresql\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/pass-a-variable-to-a-trigger-in-postgresql\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/pass-a-variable-to-a-trigger-in-postgresql\\\/\",\"name\":\"Pass a variable to a trigger in PostgreSQL - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2021-04-22T19:14:43+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/59554f0d99383431eb6ed427e338952b\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/pass-a-variable-to-a-trigger-in-postgresql\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/pass-a-variable-to-a-trigger-in-postgresql\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/pass-a-variable-to-a-trigger-in-postgresql\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Pass a variable to a trigger in PostgreSQL\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/59554f0d99383431eb6ed427e338952b\",\"name\":\"Open source Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"caption\":\"Open source Team\"},\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/open-source-team\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Pass a variable to a trigger in PostgreSQL - dbi 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":"https:\/\/www.dbi-services.com\/blog\/pass-a-variable-to-a-trigger-in-postgresql\/","og_locale":"en_US","og_type":"article","og_title":"Pass a variable to a trigger in PostgreSQL","og_description":"By Franck Pachot . With Oracle there are many ways to set a state in the session: context variables, set with DBMS_SESSION.SET_CONTEXT and retrieved with SYS_CONTEXT, or package global variables, or global or private temporary tables with ON COMMIT PRESERVE ROWS,&#8230; How would you do it in PostgreSQL? I&#8217;m taking an example from a Yugabyte [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/pass-a-variable-to-a-trigger-in-postgresql\/","og_site_name":"dbi Blog","article_published_time":"2021-04-22T19:14:43+00:00","author":"Open source Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Open source Team","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/pass-a-variable-to-a-trigger-in-postgresql\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/pass-a-variable-to-a-trigger-in-postgresql\/"},"author":{"name":"Open source Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/59554f0d99383431eb6ed427e338952b"},"headline":"Pass a variable to a trigger in PostgreSQL","datePublished":"2021-04-22T19:14:43+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/pass-a-variable-to-a-trigger-in-postgresql\/"},"wordCount":584,"commentCount":0,"keywords":["parameter","PostgreSQL","Trigger"],"articleSection":["PostgreSQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/pass-a-variable-to-a-trigger-in-postgresql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/pass-a-variable-to-a-trigger-in-postgresql\/","url":"https:\/\/www.dbi-services.com\/blog\/pass-a-variable-to-a-trigger-in-postgresql\/","name":"Pass a variable to a trigger in PostgreSQL - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2021-04-22T19:14:43+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/59554f0d99383431eb6ed427e338952b"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/pass-a-variable-to-a-trigger-in-postgresql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/pass-a-variable-to-a-trigger-in-postgresql\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/pass-a-variable-to-a-trigger-in-postgresql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Pass a variable to a trigger in PostgreSQL"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/59554f0d99383431eb6ed427e338952b","name":"Open source Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","caption":"Open source Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/open-source-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/16231","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/28"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=16231"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/16231\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=16231"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=16231"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=16231"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=16231"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}