Search code examples
sqlsqlitetriggersinsertsql-update

Trigger to update age when inserted age < 0

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;
Solution

  • 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.