CREATE  TABLE IF NOT EXISTS `ohrm_job_candidate_attachment_text` (
  `attachment_id` INT NOT NULL ,
  `candidate_id` INT NOT NULL ,
  `content` MEDIUMTEXT NULL ,
  PRIMARY KEY (`attachment_id`) ,
  FULLTEXT INDEX `ftidx_content` (`content` ASC) ,
  INDEX `fk_ohrm_job_candidate_attachment_text_1` (`attachment_id` ASC),
  INDEX `fk_ohrm_job_candidate_attachment_text_2` (`candidate_id` ASC), 
  CONSTRAINT `fk_ohrm_job_candidate_attachment_text_1`
    FOREIGN KEY (`attachment_id` )
    REFERENCES `ohrm_job_candidate_attachment` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE
)ENGINE = InnoDB default charset=utf8;

DELIMITER $$

CREATE TRIGGER `ohrm_search_inside_resume_watch_delete_attachment`
AFTER DELETE ON `ohrm_job_candidate_attachment`
FOR EACH ROW BEGIN
  DELETE FROM `ohrm_job_candidate_attachment_text` WHERE `attachment_id` = OLD.`id`;
END;$$

CREATE TRIGGER `ohrm_search_inside_resume_watch_delete_candidate`
AFTER DELETE ON `ohrm_job_candidate`
FOR EACH ROW BEGIN
  DELETE FROM `ohrm_job_candidate_attachment_text` WHERE `candidate_id` = OLD.`id`;
END;$$

CREATE TRIGGER `ohrm_search_inside_resume_clear_attachment_text`
AFTER INSERT ON `ohrm_job_candidate_attachment`
FOR EACH ROW BEGIN
  INSERT INTO `ohrm_job_candidate_attachment_text`(`attachment_id`, `candidate_id`, `content`) VALUES
            (NEW.`id`, NEW.`candidate_id`, '') ON DUPLICATE KEY UPDATE `content` = '';
END;$$

CREATE TRIGGER `ohrm_search_inside_resume_update_attachment_text`
AFTER UPDATE ON `ohrm_job_candidate_attachment`
FOR EACH ROW BEGIN
  IF NEW.`file_content` <> OLD.`file_content` THEN
    INSERT INTO `ohrm_job_candidate_attachment_text`(`attachment_id`, `candidate_id`, `content`) VALUES
            (NEW.`id`, NEW.`candidate_id`, '') ON DUPLICATE KEY UPDATE `content` = '';
  END IF;
END;$$

DELIMITER ;

