PostgreSQL Notes
PrevChapter 7. Automating processesNext

Updating a "last changed" field

You want a "last changed" timestamp on a table that gets automatically updated every time a user inserts/updates a record.

Solution

Define update/insert triggers on the table in question.

Discussion

To automatically update co_lastchg every time a company record is inserted or updated use: 

CREATE FUNCTION set_co_lastchg() RETURNS opaque AS '
BEGIN
  NEW.co_lastchg = now();
  RETURN NEW;
END;
' language 'plpgsql';

CREATE TRIGGER co_lastchg_trig BEFORE INSERT OR UPDATE ON companies FOR EACH ROW EXECUTE PROCEDURE set_co_lastchg();

You can repeat this for every timestamp to be updated.

PrevHomeNext
Recording a log of changes to a tableUpUsing PHP