MySQL trigger - offset internal order column
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
The "pgorder" column represents the position of a particular page.
I need a trigger that, after deleting a record of one page, automatically shifts (decreases) the order of the remaining pages for one position.
So when I delete eg. pgid = 2 the table should look like this:
pgid|pgname|pgorder
----+------+-------
1 |Page#1| 1
3 |Page#3| 2
4 |Page#4| 3
How should this MySQL trigger look like?
source to share
You cannot use a DML statement to modify the same table on which the trigger was fired. You will receive 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 is at risk of infinite loops, or at least dead ends. If DELETE has locked the table before firing the trigger, and then does an UPDATE inside the trigger that is acquiring a lock on the table, then none of them can continue.
See https://dev.mysql.com/doc/refman/5.6/en/stored-program-restrictions.html :
A stored function or trigger cannot modify a table that is already in use (for reading or writing) by a statement calling the function or trigger.
The correct solution is to accomplish this task in two steps: first DELETE and then 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 committing the transaction, or cancel the transaction.
source to share