SQL Trigger After Insert is not inserting values to new table2020-02-15 mysql triggers
I have a wordpress website and a plugin that I can import products with. When a product is imported, it has meta tags attached to it, like below (47226 is the product and 47227 is a variation of 47226):
When a new item is imported via the plugin, it automatically inserts all this metadata into a table called wp_postmeta.
Since each product always has '_price' , and variations have both '_price' and '_regular_price', I am trying to extract only the '_price' and '_regular_price' rows, with the respective values for post_id, meta_key and meta_value, and save these values to a new table called original_price with a trigger.
The plugin also inserts price updates into wp_postmeta for existing products in the original_price table, so I want to exclude them and only insert items that don't already exist in original_prices table.
So far my trigger is:
DELIMITER // CREATE TRIGGER `new_import_INSERT` AFTER INSERT ON `wp_postmeta` FOR EACH ROW BEGIN INSERT INTO original_prices (date_added,post_id,meta_key,meta_starting_price) SELECT now(),post_id,meta_key,meta_value FROM wp_postmeta WHERE post_id=NEW.post_id AND NEW.meta_key IN ('_price','_regular_price') AND NOT EXISTS (SELECT * FROM original_prices WHERE post_id=new.post_id); END// DELIMITER ;
This works, but for each _price and _regular_price, it duplicates the results 18 times. I tried changing the
FROM wp_postmeta to
FROM inserted , but that makes it so no entries are added to original_prices when a new product is imported via the plguin. I also tried changing the
SELECT now(),post_id,meta_key,meta_value to
SELECT now(),NEW.post_id,NEW.meta_key,NEW.meta_value but this didn't resolve the duplicate issue either.
Where am I going wrong for it to duplicate the results so many times?
If you only want that a riow is inserted when 'meta_key is _price' or '_regular_price'
So you do something like this
DELIMITER // CREATE TRIGGER `new_import_INSERT` AFTER INSERT ON `wp_postmeta` FOR EACH ROW BEGIN IF (NEW.meta_key = '_price' OR NEW.meta_key = '_regular_price') THEN INSERT INTO original_prices (date_added,post_id,meta_key,meta_starting_price) VALUES (now(),NEW.post_id,NEW.meta_key,NEW.meta_value ); END IF; END// DELIMITER ;
- Insert into a MySQL table or update if exists
- JOIN 2 tables and select specific rows
- While joining it to wp_posts, how can I put all the values of wp_postmeta table in one row?
- mySQL: selecting based on several keys in key/value table
- SQL Multiple WHERE IN
- SQL Trigger which add a new string if not exist
- PhPmyadmin SQL Query to insert meta_key & value IF another value exist