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

You can read more about almost the same approach here.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s