sql - MySQL @变量

Question

我正在尝试设置一个 MySQL 触发器，但我不知道如何才能完成我想要完成的事情。我想我需要设置一个 MySQL @variable，但我试过的方法没有用，而且我一直没能找到好的资源来解决这个问题。我在phpMyAdmin中更改了分隔符，下面的注释在实际查询中没有使用。

CREATE TRIGGER coroner AFTER INSERT ON events
FOR EACH ROW BEGIN
UPDATE teams SET live = live-1 WHERE id = NEW.victim;
UPDATE teams SET score = score+NEW.value WHERE id = NEW.shooter;
UPDATE teams SET last_active = NEW.time WHERE id = NEW.shooter OR id = NEW.victim;
// up to here all works fine
IF (SELECT live FROM teams WHERE id = NEW.victim) = 0 THEN
UPDATE teams SET slot = slot-1 WHERE slot > OLD.slot;
ENDIF;
END

我实际上在这里做的是将一个团队安排成一个圆圈，当团队的最后一名球员被淘汰后，我将在没有它的情况下关闭圆圈。

这是来自 phpMyAdmin 的错误消息:

#1363 - There is no OLD row in on INSERT trigger

谢谢你的帮助

---

新的尝试:

CREATE TRIGGER coroner AFTER INSERT ON events
FOR EACH ROW BEGIN
UPDATE teams SET live = live-1 WHERE id = NEW.victim;
UPDATE teams SET score = score+NEW.value WHERE id = NEW.shooter;
UPDATE teams SET last_active = NEW.time WHERE id = NEW.shooter OR id = NEW.victim;
SET @pivot := (SELECT slot FROM teams WHERE id = NEW.victim);
IF (SELECT live FROM teams WHERE id = NEW.victim) = 0 THEN
UPDATE teams SET slot = slot+1 WHERE slot < @pivot;
ENDIF;
END

错误:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; END' at line 9

Answer 1

触发器语言中没有 ENDIF 关键字。请改用 END IF(其中有一个空格)。

您可以查看 IF Statement 的语法文档.