sqlite3 using triggers to update age when inserted age

I am trying to set age = 0 if the age is inserted as negative. I dont know what is wrong with the code:

CREATE TRIGGER verify_age
BEFORE INSERT ON customers
FOR EACH ROW
WHEN (NEW.age < 0)
BEGIN
UPDATE customers
SET age = 0
END;

CodePudding user response：

You can try next code.

CREATE TRIGGER verify_age
BEFORE INSERT ON customers
FOR EACH ROW
WHEN NEW.age < 0
BEGIN
 SET NEW.age = 0;
END;

CodePudding user response：

Try this:

CREATE OR REPLACE TRIGGER validate_age
    BEFORE INSERT
    ON customers
    FOR EACH ROW
BEGIN
    IF :new.age < 0
    THEN
        :new.age := 0;
    END IF;
END;

or :

CREATE TRIGGER validate_age 
       BEFORE INSERT ON customers
    BEGIN
       SELECT
          CASE
        WHEN NEW.age < 0 THEN
          NEW.age = 0
           END;
    END;