Postgresql: Row Update Trigger

SYNOPSYS

This post needs a trigger to update a timestamp at each row modification.

PROCEDURE

add usr_modified field to org_users table and set the trigger
1
2
3
4
5
6
7
8
9
10
ALTER TABLE org_users ADD COLUMN usr_modified timestamp with time zone NOT NULL DEFAULT current_timestamp;

CREATE OR REPLACE FUNCTION org_users_update() RETURNS trigger AS $BODY$
BEGIN
  NEW.usr_modified := current_timestamp;
  RETURN NEW;
END;
$BODY$ LANGUAGE plpgsql;

CREATE TRIGGER org_users_update_call BEFORE UPDATE ON org_users FOR EACH ROW EXECUTE PROCEDURE org_users_update();