Trigger to update age when inserted age

I am trying to set age = 0 if the age is inserted as negative. I don't 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：

The syntactical error in your code is that it is missing a ; before END, but even if you correct this it would not solve your problem.

You need a WHERE clause in the UPDATE statement so that you update only the new row and not the whole table.
The condition in the WHERE clause will check for the rowid of the new row, but this rowid exists only after the row is inserted.
So, you must change the trigger to an AFTER INSERT trigger:

CREATE TRIGGER verify_age AFTER INSERT ON customers
  WHEN NEW.age < 0
  BEGIN
    UPDATE customers
    SET age = 0
    WHERE rowid = NEW.rowid;
  END;

See the demo.

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;