DELIMITER ;;
CREATE TRIGGER `trigger_search_update` AFTER UPDATE ON `jos_ads` FOR EACH ROW BEGIN

DECLARE currency_txt VARCHAR(5) DEFAULT NULL;  
DECLARE cur_image VARCHAR(99) DEFAULT NULL;  


DELETE FROM jos_ads_search_results WHERE ad_id = NEW.id;

IF NEW.status_pub = 'Published' THEN

 

SELECT name INTO currency_txt FROM jos_ads_currency WHERE id = NEW.currency;



IF NEW.image IS NULL THEN
SELECT default_picture INTO cur_image FROM jos_ads_categories WHERE id = NEW.category;
ELSE SET cur_image = NEW.image;
END IF;

INSERT INTO jos_ads_search_results(ad_id, title, description, image, askprice, currency, keyword) VALUES(NEW.id, NEW.title, NEW.short_description, cur_image, NEW.askprice, currency_txt, LOWER(CONCAT_WS(',', NEW.title, NEW.short_description, NEW.default_field_value)));
END IF;
END;;
DELIMITER ;

-- ----------------------------
-- Trigger structure for trigger_search_update_ogrs
-- ----------------------------
DELIMITER ;;
CREATE TRIGGER `trigger_search_update_ogrs` AFTER UPDATE ON `jos_ads_org` FOR EACH ROW BEGIN

DELETE FROM jos_ads_search_results WHERE org_id = NEW.id;

IF NEW.published = 1 THEN

INSERT INTO jos_ads_search_results(org_id, title, description, image, keyword) VALUES(NEW.id, NEW.title, NEW.short_description, NEW.picture, LOWER(CONCAT_WS(',', NEW.title, NEW.short_description)));
END IF;
END;;
DELIMITER ;