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.

  • Use a view instead. – Philᵀᴹ Dec 16 '13 at 20:05
  • I dont think a view is possible in my case, cuz i do a Mysql_fetch_row in php, and then it would call always the same view, didnt it, so there had to be on view per Object? – user3101966 Dec 16 '13 at 20:08
  • So you are aggregating the rankings, and every time a new rank is inserted, you want the aggregate recalculated and updated in the object summary table? – Ken Ingram Oct 25 '16 at 19:15

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

 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
     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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.