How to validate with before insert trigger in sqlite

sqlite triggers

if you want to check the mark whether it is less than or equal 50

CREATE TRIGGER trigger_min_mark AFTER INSERT ON examWHEN New.mark<=50BEGIN  UPDATE exam SET mark=50 WHERE id = New.id;END

Be carefull you in the when statement you must specify New.ColumnName

sqlite triggers

If you want to raise an error on invalid data, do this:

CREATE TRIGGER trigger_validate_mark BEFORE INSERT ON examWHEN mark<=50BEGIN  SELECT RAISE(ABORT,'Mark must be at least 50.');END;

If you just want to fix the data, do this:

CREATE TRIGGER trigger_min_mark AFTER INSERT ON examWHEN mark<=50BEGIN  UPDATE exam  SET mark=50  WHERE id = new.id;END;

sqlite triggers

What about ensuring it in the table definition?

CREATE TABLE exam (  ...  CHECK(mark<=50));

CodeHunter