July 5, 2011

MySQL triggers ; error 1442

MySQL Trigger is a powerful tool to provide the data consistency and accuracy among your tables or Database in general. The only disadvantage among Triggers is that they’re not able to update the same table they’re based on.

MySQL Trigger is a powerful tool to provide the data consistency and accuracy among your tables or Database in general. The only disadvantage among Triggers is that they’re not able to update the same table they’re based on. If you have ever hit the following error on your SQL updates, well, you pretty much have no luck.

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

It might seem logical to be working for a AFTER UPDATE Trigger. On a BEFORE UPDATE Trigger specially in a MyISAM table engine, the whole table is locked and the UPDATE on the same table would fail. But that’s exactly the case for AFTER UPDATE as well. I’m not sure it is because of locking too, but it will fail.

Also if you want to do a trick and CALL a STORED PROCEDURE from the trigger to do the job for you, MySQL won’t let you do this. So as I said earlier when you hit the error just gave up the Trigger, or SP solution and think of another way with a Control Script, Cron Job or the same logic being implemented in your application layer.

This error which I would call it a Bug has been reported for years, but only has been considered as a feature request !

comments powered by Disqus
comments powered by Disqus