Avatar of marrowyung
marrowyung

asked on 

MariaDB trigger doesn't work as expected.

hi,

I am studying MariaDB trigger:

this is  a trigger:

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; 

Open in new window


it will call a SP called ADD_JOB_HISTORY and what this SP does is:

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; 

Open in new window


when when I insert a record in EMPLOYEES  tables nothing created in JOB_HISTORY table, what is going on ?
MySQL ServerSQL

Avatar of undefined
Last Comment
marrowyung

8/22/2022 - Mon