{"id":662,"date":"2005-01-26T20:48:31","date_gmt":"2005-01-26T23:48:31","guid":{"rendered":"http:\/\/www.hoogervorst.ca\/arthur\/?p=662"},"modified":"2005-01-26T20:48:31","modified_gmt":"2005-01-26T23:48:31","slug":"on-a-related-note","status":"publish","type":"post","link":"http:\/\/www.hoogervorst.ca\/arthur\/?p=662","title":{"rendered":"On a related note"},"content":{"rendered":"

F<\/span>or users of Postgres 7.2 (and probably up to 7.3, but it might be compatible with all higher versions) who do not have a clue how to get those triggers going because it’s all undocumented, here goes.\n<\/p>\n

Two things you need to know first: <\/p>\n

    \n
  1. Triggers rely on functions created by users.\n
  2. Functions (in 7.2) for triggers MUST return a result of OPAQUE<\/em>.\n
  3. Triggered functions can not have parameters!\n<\/ol>\n

    Right, tell me you didn’t know that and go back to start.\n<\/p>\n

    Take the following simple (and not real-life) scenario:\n<\/p>\n

    <\/p>\n

    We have a table ‘currency’ that needs to have an exact copy, ‘back_currency’. Everytime something is added to currency, it should be copied to the backup table.\n<\/p>\n

    First we create a function called ‘currencyupdate’:\n<\/p>\n

    \n


    \nCREATE FUNCTION currencyupd() RETURNS opaque AS ‘
    \nBEGIN
    \n   insert into back_currency (
    \n    currency_code,
    \n    currency_description,
    \n    user_lada) VALUES
    \n    (
    \n     NEW.currency_code,
    \n     NEW.currency_description,
    \n     current_timestamp
    \n    );
    \n   RETURN NEW;
    \nEND;
    \n‘  LANGUAGE ‘plpgsql’ WITH (ISSTRICT);
    \n<\/PRE>\n<\/p>\n
    (note: if we were using  a DELETE\/UPDATE trigger,  older recordsets are stored in the OLD object).\n<\/p>\n
    Now we can activate the trigger by sending the following query to the Postgres server:\n<\/p>\n
    \n

    \nCREATE TRIGGER OnCurrencyUpdate BEFORE INSERT ON currency
    \n\tFOR EACH ROW EXECUTE PROCEDURE currencyupd();
    \n<\/PRE>\n<\/p>\n
    Just in case you needed to know.<\/p>\n","protected":false},"excerpt":{"rendered":"
    For users of Postgres 7.2 (and probably up to 7.3, but it might be compatible with all higher versions) who do not have a clue how to get those triggers going because it’s all undocumented, here goes. Two things you … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[4],"tags":[],"_links":{"self":[{"href":"http:\/\/www.hoogervorst.ca\/arthur\/index.php?rest_route=\/wp\/v2\/posts\/662"}],"collection":[{"href":"http:\/\/www.hoogervorst.ca\/arthur\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.hoogervorst.ca\/arthur\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.hoogervorst.ca\/arthur\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/www.hoogervorst.ca\/arthur\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=662"}],"version-history":[{"count":0,"href":"http:\/\/www.hoogervorst.ca\/arthur\/index.php?rest_route=\/wp\/v2\/posts\/662\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.hoogervorst.ca\/arthur\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=662"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.hoogervorst.ca\/arthur\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=662"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.hoogervorst.ca\/arthur\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=662"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}