CREATE TABLE IF NOT EXISTS `ohrm_onboarding_task_notification` (
  `task_id` int(11) NOT NULL,
  `change_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

ALTER TABLE `ohrm_onboarding_task_notification` ADD FOREIGN KEY ( `task_id` ) REFERENCES `ohrm_task` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ;

DELIMITER $$

DROP TRIGGER IF EXISTS `watch_insert_onboarding_task`;$$

CREATE TRIGGER watch_insert_onboarding_task
AFTER INSERT ON ohrm_task
FOR EACH ROW BEGIN

INSERT INTO `ohrm_onboarding_task_notification` (`task_id`) VALUES (new.id);

END;$$

DROP TRIGGER IF EXISTS `watch_update_onboarding_task`;$$

CREATE TRIGGER watch_update_onboarding_task
AFTER UPDATE ON ohrm_task
FOR EACH ROW BEGIN

SET @task_id = (SELECT `task_id` FROM `ohrm_onboarding_task_notification` WHERE `task_id` = new.id);

IF ((NEW.owner_num <> OLD.owner_num) AND @task_id IS NULL) THEN
    INSERT INTO `ohrm_onboarding_task_notification` (`task_id`) VALUES (NEW.id);
END IF;

END;$$


DELIMITER ;