SQL Trigger After Insert is not inserting values to new table

Question

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?

nbk · Accepted Answer · 2020-02-14 23:20:07Z

0

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 ;

edited Feb 14, 2020 at 23:20

answered Feb 14, 2020 at 23:06

nbk

49.6k8 gold badges32 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Trigger After Insert is not inserting values to new table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related