SQL Snippetes

clear_deleted_at.sql

CREATE TRIGGER clear_deleted_at BEFORE
UPDATE
  ON todo FOR EACH ROW
  WHEN (
    NOT NEW.deleted
    AND OLD.deleted IS DISTINCT
    FROM
      NEW.deleted
  ) EXECUTE PROCEDURE trigger_clear_deleted_at();

set_deleted_at.sql

CREATE TRIGGER set_deleted_at BEFORE
UPDATE
  ON todo FOR EACH ROW
  WHEN (
    NOT NEW.deleted
    AND OLD.deleted IS DISTINCT
    FROM
      NEW.deleted
  ) EXECUTE PROCEDURE trigger_set_deleted_at();

trigger_clear_deleted_at.sql

CREATE
OR REPLACE FUNCTION trigger_clear_deleted_at() RETURNS TRIGGER AS $$ BEGIN NEW.deleted_at = NULL;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

trigger_set_deleted_at.sql

CREATE
OR REPLACE FUNCTION trigger_set_deleted_at() RETURNS TRIGGER AS $$ BEGIN NEW.deleted_at = NOW();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;