Блог

У меня проблема с триггером в базе данных mysql. У меня есть таблица, такая как:

 id int not null auto_increment (PK)
parent_id int not null,
rank int not null
  

Что я пытаюсь сделать, так это использовать триггер для обновления ранга до следующего по величине 10, когда у них одинаковый parent_id, но, похоже, это не работает.

 DELIMITER $$
DROP TRIGGER IF EXISTS after_insert $$
create trigger after_insert 
after insert on mytable
FOR EACH row
BEGIN

  IF EXISTS (SELECT rank FROM mytable WHERE parent_id = new.parent_id AND id != new.id ORDER BY rank DESC LIMIT 1) THEN
  UPDATE mytable SET rank  = 10
  WHERE id = new.id;
  ELSE
  UPDATE mytable SET rank = 20
  WHERE id = new.id;
  END IF;   

END
$$
  

Я попытался присвоить переменной новый ранг и вызвать оператор update, используя это, и снова это не сработало. Я даже создал другую таблицу для регистрации того, какие значения были выбраны, и это сработало отлично, поэтому я не совсем понимаю, что происходит. Это случай, когда, хотя триггер «ПОСЛЕ ВСТАВКИ», вставка на самом деле не произошла, поэтому он не может обновить строку, которую он только что вставил? Еще одна причина, по которой я спрашиваю об этом, заключается в том, что я даже пытался обновить ранг до разных значений, например, 1 и 2, в зависимости от того, к какому оператору он переходит, но он всегда заканчивается равным 0.

Я думаю, вы на правильном пути с этой мыслью:

Это случай, когда, хотя триггер «ПОСЛЕ ВСТАВКИ», вставка на самом деле не произошла, поэтому он не может обновить строку, которую он только что вставил?

Из часто задаваемых вопросов:

B.5.9: Могут ли триггеры обращаться к таблицам?

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

В документации неясно, что то, что вы делаете, не будет работать. OTOH, в документации неясно, сработает ли то, что вы пытаетесь сделать.

Я думаю, вам было бы лучше использовать BEFORE INSERT триггер и установить NEW.rank там. Тогда новая строка будет иметь правильное rank значение, когда она фактически будет вставлена в таблицу, а не исправлена после. Кроме того, вы могли бы упростить свою проверку существования только до этого:

 EXISTS(SELECT rank FROM mytable WHERE parent_id = new.parent_id)
  

поскольку NEW.id это не имело бы полезного значения, и новой строки все равно не было бы в таблице; ORDER BY и LIMIT также не нужны, поскольку вы просто проверяете, существует ли что-то, поэтому я их удалил.

BEFORE INSERT Триггер, похоже, в любом случае лучше соответствует вашим намерениям, и это даст вам правильные данные, как только они будут вставлены в вашу таблицу.

Если вы хотите, чтобы ранг был установлен на 10 больше, чем самый высокий ранг «брата», вы могли бы использовать:

 DELIMITER $$
DROP TRIGGER IF EXISTS whatever $$
create trigger whatever
BEFORE INSERT ON mytable
FOR EACH row
BEGIN

  SET NEW.rank = 10   COALESCE( 
      ( SELECT max(rank)
        FROM mytable
        WHERE parent_id = NEW.parent_id
      ), 0 ) ;   

END
$$