Datetime Trigger

From Flo's Knowledge in a Nutshell

Postgres Trigger to fill timestamp automatically

Create Function

CREATE OR REPLACE FUNCTION update_dates()
  RETURNS TRIGGER AS
$BODY$
BEGIN
IF (TG_OP = 'INSERT') THEN
NEW.date_add = current_timestamp;
RETURN NEW;
ELSIF (TG_OP = 'UPDATE') THEN
NEW.date_modif = current_timestamp;
RETURN NEW;
END IF;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;

Add fields

ALTER TABLE my_table ADD COLUMN date_add timestamp; 
ALTER TABLE my_table ADD COLUMN date_modif timestamp;

Bind Trigger to table

CREATE TRIGGER my_trigger_any_name BEFORE INSERT OR UPDATE ON my_table FOR EACH ROW EXECUTE PROCEDURE update_dates();