开发者

Using Aggregate Functions Inside a Trigger In MySQL

开发者 https://www.devze.com 2022-12-11 06:55 出处:网络
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

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

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号