MYSQL, how to build this Trigger

Question

I have two tables in mysql

Table: objects

1: ID  (Auto Increment)         INT        eg: 1
2: Object (like "Flower)        TEXT       eg: Flower
3: Rank (like 90, calculated)   FLOAT      eg: 90

Table: Rankings

1: ID  (Auto Incement)          INT        eg: 1,2,3,4,5,
2: Object (like "Flower")       TEXT       eg; Flower,Flower,Flower,Flower,Flower
3: Person (like "Peter")        TEXT       eg: Peter,Hans,Markus,Emil,Egon
4: Ranking ( +/- Points)        FLOAT      eg: 100, -10 , -10 , -10 , 20

So, The Rank from Table1, is a sumary of all Rankings in Table2 where Object = Object.Table1. In this case it would be 90 for the Flower.

How can I build a trigger (on insert) for this? Every time someone posts a ranking the Rank.Table1 gets updated via a trigger.

Sorry for this question, but I'm very new to this.

Answer 1

Though this question is 2 years old, seems like it deserves an answer.

Make sure to look at MySQL documentation 21.3.1 Trigger Syntax and Examples in order to understand the importance and context of the delimiter statement.

Create trigger tbi_new_ranking before insert on rankings 
for each row

BEGIN
 DECLARE v_ranking INTEGER;
 DECLARE v_aggregate_ranking INTEGER;
 DECLARE v_object VARCHAR(<how many characters>);


 set v_object = NEW.object;
 /*
   Define the code you want to aggregate the ranking numbers.
   The inserting data is addressed by prefixing the triggering table's columns with
   NEW. This can be a little confusing so it is wise to set up variables
   to represent the inserted data rather than mussing about with them directly
   E.G.
     SELECT count(*) INTO v_count from Rankings where Object = NEW.Object;
     SELECT sum(Ranking) INTO v_sum from Rankings where Object = NEW.Object;
     set v_aggregate_ranking = v_sum/v_count;
 */

 update objects set rank = v_aggregate_ranking where object = v_object;
END