SQL Trigger which add a new string if not exist

3 Views

I have a table wp_postmeta 

meta_id     bigint(20)   unsigned            PRI     (NULL)   auto_increment           
post_id     bigint(20)   unsigned            MUL     0                                 
meta_key    varchar(255) utf8_unicode_ci     MUL     (NULL)                             
meta_value  longtext     utf8_unicode_ci             (NULL)

table smth like this:

Read More
meta_id     post_id       meta_key   meta_value
1           1             area       12
2           1             rooms      2
3           2             rooms      3

I need to create a trigger that will add a string into this table if not exist
with meta_key = ‘area’ and meta_value = ‘0’.
Or UPDATE where meta_key = ‘area’ and meta_value = ‘NULL’.

But…
There could be a lot of meta_key for each post_id. and meta_key = ‘area’ for each post_id is set limit 1.
But problem is that not every post_id have the meta_key = ‘area’
so firstly need to create it!!

I have something like this now..(code below) it’s a part that will give the meta_value = ‘0’ of meta_key = ‘area’.But I can’t understand how do i must create this string if it’s not exist. 

CREATE
    TRIGGER `trigger` BEFORE INSERT ON `wp_postmeta` 
    FOR EACH ROW BEGIN
    IF NEW.meta_key = 'area' AND NEW.meta_value IS NULL THEN
    SET NEW.meta_value = '0';
END IF;
END;
$$
Post Views: 3

Related posts

Leave a Reply

2 comments

  1. Since it looks like the wp_posts entry must be created before the wp_postmeta entry (otherwise you won’t know the post_id), I’d put an after insert trigger on wp_posts that inserts the wp_postmeta row.

  2. Did that:

    DELIMITER $$

USE `dev_hiponia_hipo`$$

DROP TRIGGER IF EXISTS `area_for_id`$$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    TRIGGER `dev_hiponia_hipo`.`area_for_id` AFTER INSERT ON `wp_posts`
    FOR EACH ROW BEGIN
    IF NEW.post_type = 'property' THEN
        INSERT INTO wp_postmeta (meta_key, meta_value, post_id) VALUES ('area', '0', NEW.ID);
    END IF;
    END;
$$

DELIMITER ;

    –> when i set a new posts it’s automatically set the area value => ‘0’

    Second trigger I set for wp_postmeta:

          DELIMITER $$

USE `dev_hiponia_hipo`$$

DROP TRIGGER IF EXISTS `areamodif`$$

CREATE
    /*!50017 DEFINER = 'devhiponia'@'192.168.1.%' */
    TRIGGER `areamodif` AFTER UPDATE ON `wp_postmeta` 
    FOR EACH ROW BEGIN
    IF NEW.meta_key = 'area' AND NEW.meta_value = '' THEN
        UPDATE wp_postmeta
        SET  meta_value = '0' 
        WHERE NEW.meta_key = 'area' AND meta_value = '';
    END IF;
END;
$$

DELIMITER ;




DELIMITER $$

USE `dev_hiponia_hipo`$$

DROP TRIGGER IF EXISTS `areaifdelete`$$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    TRIGGER `areaifdelete` AFTER DELETE ON `wp_postmeta`
    FOR EACH ROW BEGIN
    IF OLD.meta_key = 'area' THEN
        INSERT INTO wp_postmeta (meta_key, meta_value, post_id) VALUES ('area', '0', OLD.post_id);
    END IF;
    END;
$$

DELIMITER ;