1

I want to update a table which is also tied on a "after delete" trigger.

DELIMITER §
CREATE TRIGGER sorting
AFTER DELETE ON tb_number
FOR EACH ROW
BEGIN
UPDATE tb_number SET number1 = number1 - 1 WHERE number1 >= old.number1;
END § 
DELIMITER ;
DELETE FROM tb_number where number1 = 2;

Why does not work this? It says:

 Error Code: 1442. Can't update table 'tb_number' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

So how can I fix this with trigger? I thought, after-delete means: Once a deletion has been executed, the trigger will update...

EchoCache
  • 555
  • 2
  • 8
  • 22
  • you can't update a table in its own trigger. What problem are you trying to solve writing a trigger like that? – Quassnoi Apr 21 '16 at 22:41
  • To achieve following: "Create a trigger that updates the numbers after a number is deleted". Maybe I use two triggers, to avoid the same table constraint – EchoCache Apr 21 '16 at 22:50
  • More to the point, *why* are you trying to "Create a trigger that updates the numbers after a number is deleted"? There's probably a better solution that doesn't involve triggers at all. – Darwin von Corax Apr 22 '16 at 00:58
  • Because I want to get a change after every deletion and that's why I choosed a trigger as it starts automatically. – EchoCache Apr 22 '16 at 08:36

0 Answers0