PostgreSQL Notes
PrevChapter 7. Automating processesNext

Recording a log of changes to a table

You want to keep a log of changes made to certain tables in a separate log-table.

Solution

Use one or more triggers to trap the changes and write messages to the log table ref.

Discussion

In the example database there is a diary table that lets users record notes against a company. It would be useful if when an order is taken, the fact is recorded in the diary against that company. We'll want to trap inserts to the orders table and build an appropriate message. 

CREATE FUNCTION log_new_orders() RETURNS opaque AS '
DECLARE
  product_desc text;
  msg text;
BEGIN
  SELECT INTO product_desc pr_desc FROM products where pr_id = NEW.ord_product;
  msg := ''New order: '' || NEW.ord_qty || '' items of '' || product_desc;
  INSERT INTO DIARY (dy_company,dy_type,dy_notes) VALUES (NEW.ord_company,''ORDER'',msg);
  RETURN NEW;
END;
' language 'plpgsql';

CREATE TRIGGER order_new_log_trig AFTER INSERT ON orders FOR EACH ROW EXECUTE PROCEDURE log_new_orders();

Note the doubling of quotes in the body of the function - they are two single quotes (') not one double (“).

PrevHomeNext
Automating processesUpUpdating a "last changed" field