MySQL throwing error “Statement is incomplete expecting ;” when trying to create trigger

Question

I wanna create a trigger like this:

create trigger trigofstu before insert on student
for each row
begin
    if new.sage <18 then
        set new.sage = 18 ;
    end if;
end;

but i got the error like this:

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 5 0.016 sec

byw i used mysqlWorkbench thanks!!!

Answer 1

I have couple remarks about your code:

1. in case new.sage is null it still will be null after trigger execution
2. It's not good practice so save age in DB, it's changed in a time. Prefer to save birth-date or birth-year because it constant.

Any way, if you interesting in trigger way you can use next:

DELIMITER $$

CREATE TRIGGER trigofstu BEFORE INSERT ON test
FOR EACH ROW
BEGIN
    SET new.sage = GREATEST(18, COALESCE(new.sage, 0)) ;
END;
$$

DELIMITER ;