trigger.sql

CREATE TRIGGER `trigger JobDescriptionHook` AFTER UPDATE ON `JOB`
 FOR EACH ROW IF OLD.text_description <> NEW.text_description THEN
	CALL JobDescriptionHook(NEW.id);
END IF

DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `JobDescriptionHook`(IN `job_id_var` INT)
BEGIN

DECLARE tag_id_var INT;
DECLARE tag_name_var VARCHAR(250);
DECLARE tag_exist_in_jd INT;
DECLARE cur CURSOR FOR SELECT jt.tag_id as tag_id, t.tag as tag_name FROM TAG as t JOIN JOB_TAG as jt ON t.id = jt.tag_id
        WHERE jt.job_id = job_id_var;

OPEN cur;

BEGIN
    DECLARE exit_flag BOOLEAN DEFAULT FALSE;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_flag=True;

    cloop: LOOP    
    IF exit_flag THEN
        LEAVE cloop;
    END IF;
    FETCH cur INTO tag_id_var, tag_name_var;

    SELECT count(1) INTO tag_exist_in_jd FROM JOB WHERE LOWER(text_description) LIKE LOWER(CONCAT('% ', tag_name_var, ' %')) AND id = job_id_var;

    IF tag_exist_in_jd < 1 THEN
        DELETE FROM JOB_TAG WHERE job_id=job_id_var AND tag_id=tag_id_var;
    END IF;    

    END LOOP cloop;
END;
CLOSE cur;

END$$
DELIMITER ;