krot

trigger add rank

Apr 2nd, 2019
117
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. DROP TRIGGER `addRank`;
  2.  
  3.  
  4. DELIMITER $$
  5. CREATE TRIGGER `save_user_rank`
  6. BEFORE insert ON `user_rank`
  7. FOR EACH ROW  BEGIN
  8.  
  9.     if NEW.star>5
  10.     THEN
  11.      SET NEW.star = 5;
  12.     END IF;
  13.     if(NEW.star<=0)THEN
  14.       SET NEW.star = 1;
  15.     END IF;
  16.    
  17. END$$
  18. DELIMITER ;
  19.  
  20.  
  21. DELIMITER $$
  22. CREATE TRIGGER `addRank`
  23. AFTER insert ON `user_rank`
  24. FOR EACH ROW  BEGIN
  25.  
  26.  DECLARE k INT;
  27.  DECLARE r FLOAT;
  28.  
  29.     select (5*sum( IF(rk.star=5,1,0)) + 4*sum( IF(rk.star=4,1,0)) + 3*sum( IF(rk.star=3,1,0)) + 2*sum( IF(rk.star=2,1,0)) + 1*sum( IF(rk.star=1,1,0))),
  30.     count(  rk.id ) INTO r,k
  31.     from user_rank rk where rk.user_id=NEW.user_id;
  32.  
  33. UPDATE `users` u SET u.`rank`=round(r/k,2),u.`rank_voted`=k where u.`username`=NEW.user_id;
  34.  
  35. END$$
  36. DELIMITER ;
RAW Paste Data