DROP TRIGGER IF EXISTS `watch_insert_hs_hr_emp_attachment`;$$
CREATE TRIGGER watch_insert_hs_hr_emp_attachment AFTER  INSERT
ON  hs_hr_emp_attachment
FOR EACH ROW BEGIN
    DECLARE orangehrm_user_name VARCHAR(300) CHARSET UTF8;
    DECLARE affected_entity_name VARCHAR(300 ) CHARSET UTF8;
    DECLARE action_description VARCHAR(500 ) CHARSET UTF8;
    DECLARE new_version INT;
    SET new_version = (SELECT MAX(`version_id`) FROM `ohrm_audittrail_pim_job_record_trail` WHERE `affected_entity_id` = NEW.emp_number) + 1;
    SET new_version = IFNULL(new_version, 1);
    SET orangehrm_user_name=(SELECT IFNULL(CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname),ohrm_user.user_name) FROM ohrm_user LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = ohrm_user.emp_number WHERE ohrm_user.id = @orangehrm_user );
    SET orangehrm_user_name = IFNULL(orangehrm_user_name, 'admin');
    SET affected_entity_name=(SELECT CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname) FROM hs_hr_employee WHERE hs_hr_employee.emp_number = NEW.emp_number LIMIT 1);
    SET action_description = CONCAT_WS('','Contract attachment "' , NEW.eattach_filename , '" added.');

    IF NEW.screen ='contract'  THEN

    INSERT INTO `ohrm_audittrail_pim_job_record_trail` (`action_time`,`action_owner_id`,`action_owner_name`,`version_id`,`action`,`affected_entity_id`,`affected_entity_name`,`action_description`) VALUES (
      CURRENT_TIMESTAMP,
      @orangehrm_user,
      orangehrm_user_name,
      new_version,
      'UPDATE JOB RECORD',
      NEW.emp_number,
      affected_entity_name,
      action_description
    );

  END IF;
END;$$

DROP TRIGGER IF EXISTS `watch_update_hs_hr_emp_attachment`;$$
CREATE TRIGGER watch_update_hs_hr_emp_attachment AFTER  UPDATE
ON  hs_hr_emp_attachment
FOR EACH ROW BEGIN
    DECLARE orangehrm_user_name VARCHAR(300) CHARSET UTF8;
    DECLARE affected_entity_name VARCHAR(300 ) CHARSET UTF8;
    DECLARE action_description VARCHAR(500 ) CHARSET UTF8;
    DECLARE new_version INT;
    SET new_version = (SELECT MAX(`version_id`) FROM `ohrm_audittrail_pim_job_record_trail` WHERE `affected_entity_id` = NEW.emp_number) + 1;
    SET new_version = IFNULL(new_version, 1);
    SET orangehrm_user_name=(SELECT IFNULL(CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname),ohrm_user.user_name) FROM ohrm_user LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = ohrm_user.emp_number WHERE ohrm_user.id = @orangehrm_user );
    SET orangehrm_user_name = IFNULL(orangehrm_user_name, 'admin');
    SET affected_entity_name=(SELECT CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname) FROM hs_hr_employee WHERE hs_hr_employee.emp_number = NEW.emp_number LIMIT 1);
    SET action_description = CONCAT_WS('','Contract attachment "' , OLD.eattach_filename , '" updated to "' , NEW.eattach_filename , '".');

    IF NEW.screen ='contract'  THEN

    INSERT INTO `ohrm_audittrail_pim_job_record_trail` (`action_time`,`action_owner_id`,`action_owner_name`,`version_id`,`action`,`affected_entity_id`,`affected_entity_name`,`action_description`) VALUES (
      CURRENT_TIMESTAMP,
      @orangehrm_user,
      orangehrm_user_name,
      new_version,
      'UPDATE JOB RECORD',
      NEW.emp_number,
      affected_entity_name,
      action_description
    );

    END IF;
END;$$

DROP TRIGGER IF EXISTS `watch_delete_hs_hr_emp_attachment`;$$
CREATE TRIGGER watch_delete_hs_hr_emp_attachment AFTER  DELETE
ON  hs_hr_emp_attachment
FOR EACH ROW BEGIN
    DECLARE orangehrm_user_name VARCHAR(300) CHARSET UTF8;
    DECLARE affected_entity_name VARCHAR(300 ) CHARSET UTF8;
    DECLARE action_description VARCHAR(500 ) CHARSET UTF8;
    DECLARE new_version INT;
    SET new_version = (SELECT MAX(`version_id`) FROM `ohrm_audittrail_pim_job_record_trail` WHERE `affected_entity_id` = OLD.emp_number) + 1;
    SET new_version = IFNULL(new_version, 1);
    SET orangehrm_user_name=(SELECT IFNULL(CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname),ohrm_user.user_name) FROM ohrm_user LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = ohrm_user.emp_number WHERE ohrm_user.id = @orangehrm_user );
    SET orangehrm_user_name = IFNULL(orangehrm_user_name, 'admin');
    SET affected_entity_name=(SELECT CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname) FROM hs_hr_employee WHERE hs_hr_employee.emp_number = OLD.emp_number LIMIT 1);
    SET action_description = CONCAT_WS('','Contract attachment "' , OLD.eattach_filename , '" deleted.');

    IF OLD.screen ='contract'  THEN

    INSERT INTO `ohrm_audittrail_pim_job_record_trail` (`action_time`,`action_owner_id`,`action_owner_name`,`version_id`,`action`,`affected_entity_id`,`affected_entity_name`,`action_description`) VALUES (
      CURRENT_TIMESTAMP,
      @orangehrm_user,
      orangehrm_user_name,
      new_version,
      'UPDATE JOB RECORD',
      OLD.emp_number,
      affected_entity_name,
      action_description
    );

    END IF;
END;$$