Using Aggregate Functions Inside a Trigger In MySQL

Question

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?

Answer 1

You're calculating average for just 1 value (for each row) You'd better use SELECT AVG(age) FROM People