I am dealing with wordpress and want to produce a trigger that updates a table when these guys up-to-date. I produced the trigger and she or he labored theoretically, however it only updates the very first record and pause its execution. Variable accustomed to store a choose that returns could be among ids separated by commas.
Eq: The choose returns something similar to: 424,532,123,212
And employ it inside an update by putting "in".
UPDATE wp_posts SET e.post_modified = date (NEW.modified_date) e.ID WHERE IN (@ids);
So that as I stated it updates just the first record within this situation could be 424
If only someone is needed me.
This is actually the trigger:
CREATE TRIGGER triggerupdatedata AFTER UPDATE ON wp_ngg_gallery FOR EACH ROW BEGIN set @ids := (SELECT GROUP_CONCAT(a.ID SEPARATOR ',') FROM wp_posts a, wp_postmeta b, wp_ngg_gallery c WHERE c.gid = OLD.gid AND a.ID = b.post_id AND b.meta_key = 'galeria_id' AND c.gid = (SELECT d.meta_value FROM wp_postmeta d WHERE d.post_id = a.ID AND d.meta_key = 'galeria_id') ); UPDATE wp_posts e SET e.post_modified = date(NEW.modified_date) WHERE e.ID IN (@ids); END;//
Don't save id values right into a variable. Make use of the subquery directly within the UPDATE query. Also, like Johan stated - avoid using implicit join syntax.
I believe you've been bitten through the anti-pattern known as implicit join syntax.
It causes a variety of problems.
Build your joins explicit which means you don't finish track of mix join spaggeti.
Also SQL and CSV don't mix.
CREATE TRIGGER au_wp_ngg_gallery_each AFTER UPDATE ON wp_ngg_gallery FOR EACH ROW BEGIN UPDATE wp_posts e SET e.post_modified = date(NEW.modified_date) WHERE e.ID IN ( SELECT * FROM ( SELECT a.id FROM wp_posts p INNER JOIN wp_postmeta pm ON (pm.post_id = p.id AND pm.meta_key = 'galeria_id') INNER JOIN wp_postmeta pm2 ON (pm2.post_id = p.id AND pm2.meta_key = 'galeria_id') /* not sure if the join on pm2 is needed or not */ INNER JOIN wp_ngg_gallery ng ON (ng.gid = pm2.meta_value) WHERE ng.gid = OLD.gid ) sub) subsubhack); END //
updatea table and
selectin the same table inside a subselect.
However, you can update a table and choose in the same table inside a sub-sub-choose.
This is because a sub-sub-choose needs to operate just before the update, whereas a 'mere' subselect can run concurrent using the update, which may cause a variety of problems.