hge-blog-pg-triggers-revision-trigger.sql

-- the function to call when the trigger is invoked
CREATE FUNCTION trigger_on_note_revision()
  RETURNS TRIGGER
  LANGUAGE PLPGSQL AS $BODY$
BEGIN
  -- Create revision only if node's subject or body columns have changed
  IF OLD.title <> NEW.title OR OLD."data" <> NEW."data" THEN
    INSERT INTO note_revision (note_id, created_at, title, "data")
    VALUES (OLD.id, OLD.updated_at, OLD.title, OLD."data");
    NEW.updated_at = now();
  END IF;
  -- Return the NEW record so that update can carry on as usual
  RETURN NEW;
END; $BODY$;

-- create the trigger
CREATE TRIGGER trigger_note_revision
  BEFORE UPDATE
  ON note
  FOR EACH ROW
    EXECUTE PROCEDURE trigger_on_note_revision();