MySQL trigger - shifting internal order column

Question

I have the following table [pages]:

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

Column 'pgorder' represents position of particular page.

I need trigger which would after delete of one page record automatically shift (decrease) order of rest pages for one position.

So when I delete e.g. pgid=2 the table should look like:

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

How this MySQL trigger should look like?

Answer 1

You can't use a DML statement to modify the same table for which the trigger fired. You get this error:

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.

The reason is that it risks infinite loops, or at least deadlocks. If the DELETE locked the table before it fired the trigger, and then executes an UPDATE inside the trigger which requests a lock on the table, then neither can proceed.

See 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.

The proper solution is to do this task in two steps: first a DELETE and then subsequently an UPDATE:

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

You can do these two DML statements inside a transaction, to make sure they both succeed before you commit, or else roll back the transaction.