{"id":139,"date":"2016-03-19T12:22:19","date_gmt":"2016-03-19T11:22:19","guid":{"rendered":"https:\/\/solidt.eu\/blog\/?p=139"},"modified":"2021-05-04T11:22:55","modified_gmt":"2021-05-04T10:22:55","slug":"iseries-create-a-trigger","status":"publish","type":"post","link":"https:\/\/solidt.eu\/site\/iseries-create-a-trigger\/","title":{"rendered":"iSeries: Create a trigger"},"content":{"rendered":"\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">CREATE OR REPLACE TRIGGER RowHistoryTrigger \nAFTER UPDATE OF TableWithRows ON MySchema\nREFERENCING OLD AS old_row NEW AS new_row\nFOR EACH ROW \/*MODE DB2ROW | DB2SQL ? *\/\nWHEN (old_row.Status &lt;> new_row.Status\nBEGIN\n\tDECLARE @GUID CHAR(36) NOT NULL DEFAULT '';\n\tSET @GUID=LOWER( \n\t\tRIGHT(LPAD(HEX(INT(RAND(MICROSECOND(CURRENT_TIMESTAMP)) * 1000000000)), 8, '0'), 8) || '-' ||\n\t\tRIGHT(LPAD(HEX(INT(RAND() * 1000000000)), 8, '0'), 4) || '-' ||\n\t\tRIGHT(LPAD(HEX(INT(RAND() * 1000000000)), 8, '0'), 4) || '-' ||\n\t\tRIGHT(LPAD(HEX(INT(RAND() * 1000000000)), 8, '0'), 4) || '-' ||\n\t\tRIGHT(LPAD(HEX(INT(RAND() * 1000000000)), 8, '0'), 8) || RIGHT(LPAD(HEX(INT(RAND() * 1000000000)), 8, '0'), 4)\n\t\t);\n\n\tIF(NOT new_row.Status IS NULL) THEN\n\t\tINSERT INTO TableRowHistory (OID, INSERTEDAT, USERNAME, RECORDID, FROMSTATUS, TOSTATUS, MESSAGE) VALUES (@GUID, CURRENT_TIMESTAMP, USER, new_row.Oid, old_row.Status, new_row.Status, 'CHANGED');\n\tEND IF\nEND\n\n<\/pre>\n","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"inline_featured_image":false,"footnotes":""},"categories":[8],"tags":[],"class_list":["post-139","post","type-post","status-publish","format-standard","hentry","category-other-scripts"],"_links":{"self":[{"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/posts\/139","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/comments?post=139"}],"version-history":[{"count":3,"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/posts\/139\/revisions"}],"predecessor-version":[{"id":5035,"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/posts\/139\/revisions\/5035"}],"wp:attachment":[{"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/media?parent=139"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/categories?post=139"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/tags?post=139"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}