mysql 触发器更新不起作用

Question

DELIMITER $$
CREATE TRIGGER arrange_serial
 AFTER DELETE ON db.table
 FOR EACH ROW 
BEGIN
   UPDATE db.table set serial=serial-1 where serial > DELETED.serial;
END$$
DELIMITER ;

删除时，我收到 db.DELETED 不存在的错误。谁能建议我哪里错了。

Answer 1

您应该引用已删除行的旧值。使用OLD代替DELETED；

DELIMITER $$
CREATE TRIGGER arrange_serial
 AFTER DELETE ON db.table
 FOR EACH ROW 
 BEGIN
   UPDATE db.table set serial=serial-1 where serial > OLD.serial;
 END$$
DELIMITER ;