G2A Many GEOs
SHARE
TWEET

trigger add rank

krot Apr 2nd, 2019 90 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
Ledger Nano X - The secure hardware wallet
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Top