SQL constraint to prevent updating a column based on its prior value

Question

Can a Check Constraint (or some other technique) be used to prevent a value from being set that contradicts its prior value when its record is updated.

One example would be a NULL timestamp indicating something happened, like "file_exported". Once a file has been exported and has a non-NULL value, it should never be set to NULL again.

Another example would be a hit counter, where an integer is only permitted to increase, but can never decrease.

If it helps I'm using postgresql, but I'd like to see solutions that fit any SQL implementation

Answer 1

Use a trigger. This is a perfect job for a simple PL/PgSQL ON UPDATE ... FOR EACH ROW trigger, which can see both the NEW and OLD values.

See trigger procedures.

Answer 2

lfLoop has the best approach to the question. But to continue Craig Ringer's approach using triggers, here is an example. Essentially, you are setting the value of the column back to the original (old) value before you update.

CREATE OR REPLACE FUNCTION example_trigger()
  RETURNS trigger AS
$BODY$
BEGIN
     new.valuenottochange := old.valuenottochange;
     new.valuenottochange2 := old.valuenottochange2;
     RETURN new;
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;



DROP TRIGGER IF EXISTS trigger_name ON tablename;
  CREATE TRIGGER trigger_name BEFORE UPDATE ON tablename
    FOR EACH ROW EXECUTE PROCEDURE example_trigger();