I have a 'People' table with several attributes including 'age'. Each time I insert a new tuple into this table, I would like to find out the average age of all the people listed in the table. If the average is above 50, I want to modify the age in the tuple being inserted. I'm using a 'BEFORE INSERT' trigger for this. Here is the test code I currently have (you can ignore the 'delimiter' lines):
delimiter |
CREATE TRIGGER checkAge BEFORE INSERT ON People
FOR EACH ROW BEGIN
开发者_运维问答 IF AVG(age) > 50 THEN
SET NEW.age = 20;
END IF;
END
|
delimiter ;
What am I doing wrong?
You're calculating average for just 1 value (for each row) You'd better use SELECT AVG(age) FROM People
精彩评论