Is it possible to use a trigger to delete data from the table that the trigger is based upon?

Richard Quadling
Richard Quadling used Ask the Experts™
on
Hi.

I have a table which acts as a short term cache of some status information. Each row in the table holds the number of images to process and the number of processed images.

I have multiple servers writing to this table (Amazon EC2 setup).

When all the images are processed, I want to delete the row.

So, conventionally,
DELETE FROM
 `datafeeds`.`im_retailerimageprocessing`
WHERE
 `ImagesToProcess` = `ImagesProcessed`;

Open in new window


Putting that in a AFTER UPDATE ON trigger, I get an error upon execution :
Error Code: 1142. Can't update table 'im_retailerimageprocessing' in stored function/trigger because it is already used by statement which invoked this function/trigger.

Open in new window


For me, as I'm using it in an AFTER statement, there should be no conflict. But, obviously, I'm wrong and I don't know how to achieve the desired result.

I don't want to modify the app as it is very simple and just "ticks" a box when it has done its job. The scheduler/dispatcher deals with creating the threads and I want to use the DB to keep all the DB uptodate.

What am I missing?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
I assume the fields is updated once the image processing is finished.  So, your trigger will be when the ImagesToProcess field (I gather) is changed to ImagesProcessed.

I'm a little rusty, but the trigger should look like this:

CREATE TRIGGER trigger_name 
AFTER UPDATE ON table_name 
FOR EACH ROW 
DELETE FROM table_name WHERE field_name = "image_deleted_flag";

Open in new window

Richard QuadlingSenior Software Developer

Author

Commented:
Did I miss something in my explanation? I can't delete from the table that the trigger is attached to.

Both columns are numeric and are simply counts.

I want to delete the row when the counts match as there is nothing left to do at that stage.
Top Expert 2012
Commented:
I'm not sure if MySQL triggers have a way to handle that.  While I really think you should just modify the app, one workaround is to use MySQL Event Scheduler to do a regular batch job for the deletion.  I'm just on an ipad right now so it's hard to type code (which I also cannot test) but see the thread at http://www.experts-exchange.com/Database/MySQL/Q_27663309.html - it should give you all the info you need to create an event.

Commented:
Sorry I misunderstood.  Also, I forgot that what you're trying to do doesn't work with mysql.  At least on my version, I don't know if it's been changed in newer releases.

So, pay me no mind and go with what johanntagle is recommending.
Richard QuadlingSenior Software Developer

Author

Commented:
The application was changed. A single entry so easy to accomplish. Every one is happy. Increased throughput by 12 for a single server and enabled elastic scalability. So really improved things MANY MANY times over the single monolithic approach.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial