[PostgreSQL Trigger data copy to transactional on insert/update] #postgresql #function #trigger #update #insert #example #sql #history

transacional_store.md

Function definition, where:

• fl_hist_portfolio is a transactional table.
• coin, locked, free and updated are the fields to be updated on the transactional datastore.
• NEW.coin, NEW.locked, NEW.free and NEW.updated refer to the new inserted data.

CREATE OR REPLACE FUNCTION fl_history_portfolio ()
RETURNS TRIGGER
AS
$$
BEGIN
  INSERT INTO fl_hist_portfolio
              (coin, locked, free, updated )
              VALUES (NEW.coin, NEW.locked, NEW.free, NEW.updated);
  RETURN NEW;
END;
$$
LANGUAGE plpgsql;

Trigger definition, where:

• "AFTER INSERT OR UPDATE" will run the previous function after each insert or update.
• fl_portfolio is de table we want this trigger to "monitor"

CREATE TRIGGER portfolio_history
       AFTER INSERT OR UPDATE
       ON fl_portfolio
       FOR EACH ROW
       EXECUTE PROCEDURE fl_history_portfolio();