MySQL触发器 - 移动内部订单列

Question

我有下表[页数]:

pgid|pgname|pgorder
----+------+-------
  1 |Page#1|   1
  2 |Page#2|   2
  3 |Page#3|   3
  4 |Page#4|   4

“pgorder”列表示特定页面的位置。

我需要触发器，它会在删除一页记录后自动移动(减少)一个位置的其余页面的顺序。

所以当我删除例如pgid=2 表应该如下所示:

pgid|pgname|pgorder
----+------+-------
  1 |Page#1|   1
  3 |Page#3|   2
  4 |Page#4|   3

这个 MySQL 触发器应该是什么样子的？

Answer 1

您不能使用 DML 语句修改触发触发器的同一个表。你得到这个错误:

ERROR 1442 (HY000): Can't update table 'pages' in stored function/trigger because 
it is already used by statement which invoked this stored function/trigger.

原因是它有无限循环的风险，或者至少是死锁。如果 DELETE 在触发触发器之前锁定了表，然后在触发器内执行请求锁表的更新，则两者都无法继续。

参见 https://dev.mysql.com/doc/refman/en/stored-program-restrictions.html :

A stored function or trigger cannot modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.

正确的解决方案是分两步完成此任务:首先是删除，然后是更新:

DELETE FROM pages WHERE pgorder = 3;
UPDATE pages SET pgorder = pgorder-1 WHERE pgorder > 3;

您可以在事务中执行这两个 DML 语句，以确保它们在您提交之前都成功，否则回滚事务。