Is it possible to use a trigger to delete data from the table that the trigger is based upon?
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,
Putting that in a AFTER UPDATE ON trigger, I get an error upon execution :
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?
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`;
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.
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?
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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:
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";
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.
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.
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial