asked on
CREATE DEFINER=`root`@`%` TRIGGER `HR`.`UPDATE_JOB_HISTORY` AFTER UPDATE ON HR.EMPLOYEES FOR EACH ROW
Begin
IF (NEW.job_id <> OLD.job_id) AND(NEW.department_id <> OLD.department_id) then
CALL HR.ADD_JOB_HISTORY(OLD.EMPLOYEE_ID,OLD.HIRE_DATE,CURRENT_TIMESTAMP,OLD.JOB_ID,OLD.DEPARTMENT_ID);
end if;
end;
DROP PROCEDURE IF EXISTS HR.ADD_JOB_HISTORY;
CREATE PROCEDURE HR.`ADD_JOB_HISTORY`(P_EMP_ID MEDIUMINT
, P_START_DATE DATETIME
, P_END_DATE DATETIME
, P_JOB_ID VARCHAR(10)
, P_DEPARTMENT_ID SMALLINT)
BEGIN
INSERT INTO JOB_HISTORY(EMPLOYEE_ID, START_DATE, END_DATE,
JOB_ID, DEPARTMENT_ID)
VALUES(P_EMP_ID, P_START_DATE, P_END_DATE, P_JOB_ID, P_DEPARTMENT_ID);
END;