SQL to prevent a column from updating based on its previous value

Question

Is it possible to limit the check (or some other method) to prevent the setting of a value that contradicts its previous value when updating its record.

One example would be a NULL timestamp indicating what happened, such as "file_exported". After the file has been exported and has a value other than NULL, it should never be set to NULL again.

Another example is the hit counter, where an integer is allowed to increase, but it can never decrease.

If this helps, I use postgresql, but I would like to see solutions that match any SQL implementation

Answer 1

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

Another example is the hit counter, where an integer is allowed to increase, but can never decrease.

In both cases, I simply did not record these changes as attributes in the annotated table; An “exported” or “hit counter” is a great idea representing related but orthogonal real-world concepts from the objects to which they relate:

So they would just be a different relationship. Since we want file_exported to occur once:

CREATE TABLE thing_file_exported( thing_id INTEGER PRIMARY KEY REFERENCES(thing.id), file_name VARCHAR NOT NULL ) 

Hit counter is similar to another table:

 CREATE TABLE thing_hits( thing_id INTEGER NOT NULL REFERENCES(thing.id), hit_date TIMESTAMP NOT NULL, PRIMARY KEY (thing_id, hit_date) ) 

And you can request using

 SELECT thing.col1, thing.col2, tfe.file_name, count(th.thing_id) FROM thing LEFT OUTER JOIN thing_file_exported tfe ON (thing.id = tfe.thing_id) LEFT OUTER JOIN thing_hits th ON (thing.id = th.thing_id) GROUP BY thing.col1, thing.col2, tfe.file_name 

Answer 2

Use a trigger. This is ideal for a simple PL / PgSQL ON UPDATE ... FOR EACH ROW trigger that can see NEW and OLD values.

See trigger procedures .

Answer 3

lfLoop has a better approach to the issue. But to continue Craig Ringer's approach using triggers, here is an example. Essentially, before updating, you set the column value to the original (old) value.

 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(); 

Answer 4

Stored procedures and functions in PostgreSQL have access to both old and new values, and this code can access arbitrary tables and columns. It is easy to build simple (raw?) Machines of the final state in stored procedures. This way you can even create machines with managed tables.