Inserting a row ONLY if the previous row is different

Question

I'm trying to make a database that keeps track of historical edits. It should try to mimic this behaviour:

table_name
id     name     date_updated    
123131 name1 jan-1-2021
123131 name1 jan-2-2021 <- reject, do not insert as name has not changed since the last date
123131 name2 jan-3-2021 <- accept
123131 name1 jan-4-2021 <- accept, name has changed since the last entry

Is there any way to enforce this behaviour with constraints or keys? I'm open to changing the schema and adding tables

Answer 1

First, you must change the format of the dates in the column date_updated to YYYY-MM-DD so that it is comparable.

Then you need a trigger that checks the columns date_updated and name and it will abort the insertion of the new row if either the new date_updated is less or equal to the last inserted date_updated or if the new name is equal to the last inserted name:

CREATE TRIGGER trg_trigger_name BEFORE INSERT ON table_name
BEGIN
  SELECT
    CASE
      WHEN EXISTS (
             SELECT 1
             FROM (SELECT name, date_updated FROM table_name WHERE id = NEW.id ORDER BY date_updated DESC LIMIT 1) t
             WHERE t.date_updated >= NEW.date_updated OR t.name IS NEW.name 
           )
        THEN RAISE (ABORT, 'Not allowed')
    END;
END;

See a simplified demo.