Mysql обновляет другую таблицу с помощью триггера

Пара замечаний ...

Я могу с уверенностью гарантировать, что не будет НИКАКИХ строк, удовлетворяющих условиям

  FROM video_ratings
 WHERE RATINGS='0' 
   AND RATINGS='1'
   AND RATINGS='2'

Подумай об этом. Если одно из этих условий (сравнений) оценивается как ИСТИНА, тогда другие сравнения будут оцениваться как ЛОЖЬ, и

  TRUE  AND  FALSE  AND  FALSE 

будет оцениваться как ЛОЖЬ.

Таким образом, агрегат COUNT () для этого оператора будет оцениваться как 0.

В этом заявлении UPDATE

update video_upload set RATE=TOTAL;

если все прошло успешно, это обновит строку каждый в таблице video_upload. Похоже, мы хотели бы обновить только строку один в таблице video_upload, строку со значением ID, которое соответствует VID_ID строки, которую мы только что вставили в таблицу videos_ratings.

Мы можем получить значение столбца VID_ID только что вставленной строки, указав

 NEW.VID_ID 

в корпусе спускового крючка. Вероятно, нам нужен оператор обновления, который выглядит примерно так:

UPDATE video_upload v
   SET ...
 WHERE v.ID = NEW.VID_ID ; 

Если мы хотим присвоить значения столбцам RATE_BAD, RATE_AVERAGE, RATE_GOOD и RATE_BEST, нам понадобится предложение SET для ссылки на эти столбцы ...

UPDATE video_upload v
   SET v.RATE_BAD      = some_expr 
     , v.RATE_AVERAGE  = another_expr
     , v.RATE_GOOD     = expr_for_good
     , v.RATE_BEST     = expr_for_best
 WHERE v.ID = NEW.VID_ID ; 

Возможно, мы хотели сделать что-то вроде этого, чтобы получить количество оценок и сохранить их в локальных переменных, чтобы мы могли ссылаться на них позже в триггере.

SELECT IFNULL(SUM(r.RATINGS='0'),0) AS cnt_r0
     , IFNULL(SUM(r.RATINGS='1'),0) AS cnt_r1 
     , IFNULL(SUM(r.RATINGS='2'),0) AS cnt_r2 
     , IFNULL(SUM(r.RATINGS='3'),0) AS cnt_r3 
  FROM video_ratings r 
 WHERE r.VID_ID = NEW.VID_ID
   INTO li_cnt_r0
      , li_cnt_r1
      , li_cnt_r2
      , li_cnt_r3
;

следовать за

Я рекомендую этот шаблон для имен триггеров: table_name + _suffix

где _suffix - это одно из '_ad', '_ai', '_au', '_bd', '_bi', '_bu' (для после / до удаления / вставки / обновления)

Следование этому соглашению об именах позволяет избежать конфликтов имен, и когда мы будем искать триггеры в таблице, мы будем знать, где их найти. Если перечислить триггеры в алфавитном порядке, все триггеры для данной таблицы будут сгруппированы вместе по table_name (в основном). (Крайний случай, когда у нас есть вероятность некоторого смешения, это имя таблицы, которое начинается с имени другой таблицы, за которым следует _a .. или _b ..)

(На ранней стадии разработки, когда у вас есть две таблицы и полдюжины триггеров, преимущество такого соглашения об именах неочевидно. Но оно становится очевидным, когда база данных содержит множество таблиц и триггеров.)

Также обратите внимание, что MySQL поддерживает только один триггер для каждого из BEFORE / AFTER INSERT / UPDATE / DELETE в данной таблице.

Используйте локальные переменные вместо пользовательских переменных, если нет особой причины для использования пользовательской переменной.

DELIMITER $$

DROP TRIGGER IF EXISTS video_ratings_ad$$

CREATE TRIGGER video_ratings_ad
AFTER DELETE ON video_ratings
FOR EACH ROW
BEGIN
   -- declare local variables
   DECLARE li_cnt_r0  BIGINT;
   DECLARE li_cnt_r1  BIGINT;
   DECLARE li_cnt_r2  BIGINT;
   DECLARE li_cnt_r3  BIGINT;
   -- get counts of ratings for specific VID_ID
   -- and store counts in local variables 
   SELECT IFNULL(SUM(r.RATINGS='0'),0) AS cnt_r0
        , IFNULL(SUM(r.RATINGS='1'),0) AS cnt_r1
        , IFNULL(SUM(r.RATINGS='2'),0) AS cnt_r2
        , IFNULL(SUM(r.RATINGS='3'),0) AS cnt_r3
     FROM video_ratings r
    WHERE r.VID_ID = OLD.VID_ID
     INTO li_cnt_r0
        , li_cnt_r1
        , li_cnt_r2
        , li_cnt_r3
   ;
   -- update target table with rating counts from local variables
   UPDATE video_upload t
      SET t.RATE_BAD      = li_cnt_r0
        , v.RATE_AVERAGE  = li_cnt_r1
        , v.RATE_GOOD     = li_cnt_r2
        , v.RATE_BEST     = li_cnt_r3
    WHERE t.ID = OLD.VID_ID
   ;
END$$

DROP TRIGGER IF EXISTS video_ratings_ai$$

CREATE TRIGGER video_ratings_ai
AFTER UPDATE ON video_ratings
FOR EACH ROW
BEGIN
   -- declare local variables
   DECLARE li_cnt_r0  BIGINT;
   DECLARE li_cnt_r1  BIGINT;
   DECLARE li_cnt_r2  BIGINT;
   DECLARE li_cnt_r3  BIGINT;
   -- get counts of ratings for specific VID_ID
   -- and store counts in local variables 
   SELECT IFNULL(SUM(r.RATINGS='0'),0) AS cnt_r0
        , IFNULL(SUM(r.RATINGS='1'),0) AS cnt_r1
        , IFNULL(SUM(r.RATINGS='2'),0) AS cnt_r2
        , IFNULL(SUM(r.RATINGS='3'),0) AS cnt_r3
     FROM video_ratings r
    WHERE r.VID_ID = NEW.VID_ID
     INTO li_cnt_r0
        , li_cnt_r1
        , li_cnt_r2
        , li_cnt_r3
   ;
   -- update target table with rating counts from local variables
   UPDATE video_upload t
      SET t.RATE_BAD      = li_cnt_r0
        , v.RATE_AVERAGE  = li_cnt_r1
        , v.RATE_GOOD     = li_cnt_r2
        , v.RATE_BEST     = li_cnt_r3
    WHERE t.ID = NEW.VID_ID
   ;
END$$

DROP TRIGGER IF EXISTS video_ratings_au$$

CREATE TRIGGER video_ratings_au
AFTER UPDATE ON video_ratings
FOR EACH ROW
BEGIN
   -- declare local variables
   DECLARE li_cnt_r0  BIGINT;
   DECLARE li_cnt_r1  BIGINT;
   DECLARE li_cnt_r2  BIGINT;
   DECLARE li_cnt_r3  BIGINT;
   IF( OLD.RATINGS <=> NEW.RATINGS 
   -- if VID_ID and RATINGS is not changed, we can skip getting counts
   IF( NEW.VID_ID <=> OLD.VID_ID AND NEW.RATINGS <=> OLD.RATINGS )
   THEN BEGIN END
   ELSE
      -- get counts of ratings for OLD.VID_ID
      -- and store counts in local variables
      SELECT IFNULL(SUM(r.RATINGS='0'),0) AS cnt_r0
           , IFNULL(SUM(r.RATINGS='1'),0) AS cnt_r1
           , IFNULL(SUM(r.RATINGS='2'),0) AS cnt_r2
           , IFNULL(SUM(r.RATINGS='3'),0) AS cnt_r3
        FROM video_ratings r
       WHERE r.VID_ID = OLD.VID_ID
        INTO li_cnt_r0
           , li_cnt_r1
           , li_cnt_r2
           , li_cnt_r3
      ;
      -- update target table with rating counts from local variables
      UPDATE video_upload t
         SET t.RATE_BAD      = li_cnt_r0
           , v.RATE_AVERAGE  = li_cnt_r1
           , v.RATE_GOOD     = li_cnt_r2
           , v.RATE_BEST     = li_cnt_r3
       WHERE t.ID = OLD.VID_ID
      ;
      IF( NEW.VID_ID <=> OLD.VID_ID )
      THEN BEGIN END
      ELSE
         -- get counts of ratings for specific VID_ID
         -- and store counts in local variables 
         SELECT IFNULL(SUM(r.RATINGS='0'),0) AS cnt_r0
              , IFNULL(SUM(r.RATINGS='1'),0) AS cnt_r1
              , IFNULL(SUM(r.RATINGS='2'),0) AS cnt_r2
              , IFNULL(SUM(r.RATINGS='3'),0) AS cnt_r3
           FROM video_ratings r
          WHERE r.VID_ID = NEW.VID_ID
           INTO li_cnt_r0
              , li_cnt_r1
              , li_cnt_r2
              , li_cnt_r3
         ;
         -- update target table with rating counts from local variables
         UPDATE video_upload t
            SET t.RATE_BAD      = li_cnt_r0
              , v.RATE_AVERAGE  = li_cnt_r1
              , v.RATE_GOOD     = li_cnt_r2
              , v.RATE_BEST     = li_cnt_r3
          WHERE t.ID = NEW.VID_ID
         ;
      END IF;
   END IF;
END$$

DELIMITER ;