MySQL Error 1064 42000 while trying to create a trigger-CodePudding

CREATE TRIGGER moving_average 
AFTER INSERT
ON filtered_data FOR EACH ROW
BEGIN 
INSERT INTO moving_average_table (pollutant_id,pollutant_value,lastUpdated,station_id)
VALUES (new.pollutant_id,avg(new.pollutant_value),new.lastUpdated,new.station_id)
END;

I am trying to create a trigger on the table filtered_data with this piece of code, is it syntactically wrong? I wonder why I keep on getting a syntax error 1064 42000. Any help would be appreciated.

CodePudding user response：

I tried this, it looks work for me,

delimiter //
CREATE TRIGGER moving_average 
AFTER INSERT
ON filtered_data FOR EACH ROW
 begin
INSERT INTO moving_average_table (pollutant_id,pollutant_value,lastUpdated,station_id)
VALUES (new.pollutant_id,avg(new.pollutant_value),new.lastUpdated,new.station_id) ;
end//
delimiter ;

according to this https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html

if you use the mysql program to define a trigger that executes multiple statements, it is necessary to redefine the mysql statement delimiter so that you can use the ; statement delimiter within the trigger definition.

you should remove the begin...end or redefine the delimiter