How to check with insert trigger in sqlite

Question

I searched on the Internet how to use "before starting" in sqlite. I correctly know about use "after the trigger". I found one that said “before trigger” was used to check before any changes made to the database (insert / updat line in the table). To clear the expression, I wrote a trigger in which the type is before and the event inserts to check the label before insertion. My trigger

create trigger mark_insert before insert on exam when mark<=50 begin insert into exam(mark) values(50); end; 

Perhaps this is the wrong trigger. So, could you please point out errors to me? I want to check the mark that is less than or equal to 50. If so, I want to insert a default value (in this case 50). I am not very familiar with sql and trigger and am starting to learn.

My exam table has the following columns,

 id(pk) | subject | mark 

Answer 1

How to provide it in a table definition?

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

Answer 2

If you want to raise invalid data, do the following:

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

If you want to correct the data, do the following:

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

Answer 3

if you want to check the mark of less than or equal to 50

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

Be careful in the when statement that you must specify New.ColumnName