Guest User

Untitled

a guest
Apr 30th, 2021
55
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. --table
  2. CREATE TABLE `match` (
  3.   `id` int unsigned NOT NULL AUTO_INCREMENT,
  4.   `homeId` int DEFAULT NULL,
  5.   `awayId` int DEFAULT NULL,
  6.   `date` date NOT NULL,
  7.   `time` time(3) NOT NULL,
  8.   `winner` int DEFAULT NULL,
  9.   `tournamentId` int DEFAULT NULL,
  10.   `created_by` int DEFAULT NULL,
  11.   `updated_by` int DEFAULT NULL,
  12.   `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  13.   `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  14.   PRIMARY KEY (`id`)
  15. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
  16.  
  17. --procedure
  18. CREATE DEFINER=`root`@`localhost` PROCEDURE `match_procedure`(IN winner INT, IN homeId INT, IN awayId INT)
  19. BEGIN
  20.     IF (homeId = awayId) THEN
  21.         SIGNAL SQLSTATE '45000'
  22.             SET MESSAGE_TEXT = 'Invalid team id\'s';
  23.     END IF;
  24.     IF (winner != null) THEN
  25.         IF ((winner != homeId) AND (winner != awayId)) THEN
  26.         SIGNAL SQLSTATE '45000'
  27.             SET MESSAGE_TEXT = 'Winner id\'s not valid';
  28.         END IF;
  29.     END IF;
  30. END
  31.  
  32. --trigger before insert
  33. CREATE DEFINER=`root`@`localhost` TRIGGER `match_BEFORE_INSERT` BEFORE INSERT ON `match` FOR EACH ROW BEGIN
  34.     CALL match_procedure(NEW.winner, NEW.homeId, NEW.awayId);
  35. END
  36.  
  37. --trigger after insert
  38. CREATE DEFINER=`root`@`localhost` TRIGGER `match_AFTER_INSERT` AFTER INSERT ON `match` FOR EACH ROW BEGIN
  39.     DECLARE _leaderboardId INT;
  40.     SET _leaderboardId = (SELECT lb.id from `strapi-league`.`leaderboard` lb join `strapi-league`.`tournament` t on lb.tournamentId = t.id WHERE t.id = new.tournamentId);
  41.  
  42.     IF NOT EXISTS (SELECT * FROM `strapi-league`.`teamleaderboard` t where (t.teamId = NEW.homeId) AND (t.leaderboardId = _leaderboardId)) THEN
  43.         CALL teamleaderboard_procedure(NEW.homeId, NEW.tournamentId);
  44.     END IF;
  45.     IF NOT EXISTS (SELECT * FROM `strapi-league`.`teamleaderboard` t where (t.teamId = NEW.awayId) AND (t.leaderboardId = _leaderboardId)) THEN
  46.         CALL teamleaderboard_procedure(NEW.awayId, NEW.tournamentId);
  47.     END IF;
  48.    
  49.     IF (NEW.winner IS NOT NULL AND NEW.winner = NEW.homeId) THEN
  50.         UPDATE `strapi-league`.`teamleaderboard` SET wins = wins + 1 WHERE (teamId = NEW.homeId AND leaderboardId = _leaderboardId);
  51.         UPDATE `strapi-league`.`teamleaderboard` SET losses = losses + 1 WHERE (teamId = NEW.awayId AND leaderboardId = _leaderboardId);
  52.     END IF;
  53.    
  54.     IF (NEW.winner IS NOT NULL AND NEW.winner = NEW.awayId) THEN
  55.         UPDATE `strapi-league`.`teamleaderboard` SET wins = wins + 1 WHERE (teamId = NEW.awayId AND leaderboardId = _leaderboardId);
  56.         UPDATE `strapi-league`.`teamleaderboard` SET losses = losses + 1 WHERE (teamId = NEW.homeId AND leaderboardId = _leaderboardId);
  57.     END IF;
  58. END
  59.  
  60.  
  61.  
RAW Paste Data