{"id":3165,"date":"2019-09-05T22:21:36","date_gmt":"2019-09-05T20:21:36","guid":{"rendered":"http:\/\/van-maanen.com\/?p=3165"},"modified":"2019-09-05T22:21:36","modified_gmt":"2019-09-05T20:21:36","slug":"keeping-a-journal-in-oracle","status":"publish","type":"post","link":"http:\/\/archief.van-maanen.com\/?p=3165","title":{"rendered":"Keeping a journal in Oracle"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">The idea is actually quite simple. Whenever a record in a table (here: employee) is updated with a new value in a field (here: salary), a trigger gets fired. This trigger stored the old and new value of salary in a seperate table. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">I like this trigger. It is simple. It is straightforward.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">create or replace TRIGGER employee_journal\n  AFTER INSERT OR UPDATE OF salary ON employee\n  FOR EACH ROW\nBEGIN\n  INSERT INTO audit_entry \n    (entry_date, entry_user, entry_text, old_value, new_value)\n  VALUES\n    (SYSDATE, USER, 'Salary Update ' || :NEW.ssn, \n     :OLD.salary,\n     :NEW.salary);\nEND;<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">An alternative is a trigger that calls a stored procedure:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">create or replace TRIGGER employee_journal2\n  AFTER INSERT OR UPDATE OF salary ON employee\n  FOR EACH ROW\n  call sp_employee_journal(:OLD.salary,:NEW.salary,:NEW.SSN)<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">with the stored procedure:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">create or replace procedure  sp_employee_journal (oud IN employee.salary%type, nieuw IN employee.salary%type, ssn IN employee.ssn%type) as\nbegin\n  INSERT INTO audit_entry \n    (entry_date, entry_user, entry_text, old_value, new_value)\n  VALUES\n    (SYSDATE, USER, 'Salary Update ' || ssn,oud,nieuw);\nend;<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>The idea is actually quite simple. Whenever a record in a table (here: employee) is updated with a new value in a field (here: salary), a trigger gets fired. This trigger stored the old and new value of salary in a seperate table. I like this trigger. It is simple. It is straightforward. create or [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-3165","post","type-post","status-publish","format-standard","hentry","category-allgemein"],"_links":{"self":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/3165","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3165"}],"version-history":[{"count":0,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/3165\/revisions"}],"wp:attachment":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3165"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3165"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3165"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}