DELIMITER $$ CREATE TRIGGER trig_ins_dept_mng AFTER INSERT ON dept_manager FOR EACH ROW BEGIN DECLARE v_curr_salary int; SELECT MAX(salary) INTO v_curr_salary FROM salaries WHERE emp_no = NEW.emp_no; IF v_curr_salary IS NOT NULL THEN UPDATE salaries SET to_date = SYSDATE() WHERE emp_no = NEW.emp_no and to_date = NEW.to_date; INSERT INTO salaries VALUES (NEW.emp_no, v_curr_salary + 20000, NEW.from_date, NEW.to_date); END IF; END $$ DELIMITER ; --------------------------- Hi. I was reading the code in the section 'MySQL Triggers' and wondering why 'UPDATE salaries SET to_date = SYSDATE() WHERE emp_no = NEW.emp_no and to_date = NEW.to_date;' was used in the code. In the 'Salaries' table, I don't see any updated date(today's date) in the 'to_date' column. What's the function of this part of this code? It seems the whole code would work without it as well. Thanks!

Hi Minhee! Thanks for reaching out. The idea with this part of the entire query is to use the current date (which is obtained after executing SYSDATE() ) and assign it to the new employee in case the v_curr_salary object isn't empty (which refers to IF v_curr_salary IS NOT NULL THEN from our code). Hope this helps but please feel free to get back to us should you need further assistance. Thank you.Best,Martin

Follow this topic

MINHEE SUNG

Last answered:

16 Jan 2021

Posted on:

13 Jan 2021

SQL - MySQL Triggers

1 answers ( 0 marked as helpful)

Martin Ganchev

Instructor

Posted on:

16 Jan 2021

SQL - MySQL Triggers

Submit an answer