我有一张桌子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)
像这样的表:
meta_id post_id meta_key meta_value
1 1 area 12
2 1 rooms 2
3 2 rooms 3
我需要创建一个触发器,如果meta_key ='area'和meta_value ='0'不存在,它将添加一个字符串到该表中。或UPDATE,其中meta_key ='area'和meta_value ='NULL'。
但是...每个post_id可能有很多meta_key。并且每个post_id的meta_key ='area'设置为限制1。但是问题在于,并非每个post_id都具有meta_key ='area',因此首先需要创建它!
我现在有这样的东西..(下面的代码)这部分将提供meta_value ='0'的meta_key ='area'。但是我不明白如果不存在该字符串我该如何创建。
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;
$$
做过某事:
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 ;
->当我设置一个新帖子时,它会自动设置区域值=>'0'
我为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 ;
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句