3

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?

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
sbrbot
  • 6,169
  • 6
  • 43
  • 74

1 Answers1

3

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.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • That's exactly what I do now, inside one transaction I update other and delete wanted record, but that's exactly what I wanted to get rid of with this trigger (to transfer this thing to RDBMS since here's no big business logic in it). – sbrbot Sep 13 '14 at 19:21
  • I understand, but unfortunately you cannot do this with a trigger in MySQL. – Bill Karwin Sep 13 '14 at 19:35
  • Another comment I would add is that if you only need the rows to stay in order, there should be no need to renumber them. – Bill Karwin Sep 13 '14 at 21:19
  • Yeah, of course, I know that numbers do not have to be sequential, but if web interface of my CMS solution I offer users to manually define order of their articles so I'd like to avoid gaps among order numbers. – sbrbot Sep 14 '14 at 05:58
  • Thank you very much Bill for your help, at the beginning I was suspicious about that this was not achievable in MySQL, I created my own trigger and got exactly the same error. Then I realized your big reputation on StackOverflow and saw that you're the author of Book related to DB stuff. I will read your book and expect to see you here again in some of my next questions. :-) – sbrbot Sep 14 '14 at 06:11
  • Thanks for that, I hope you enjoy my book. Yeah, trying the trigger yourself is the best and most dependable way to confirm the behavior. – Bill Karwin Sep 14 '14 at 06:51