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

友情链接