🛠️ Scheduled Maintenance | We’ll be undergoing scheduled maintenance and upgrades between 00:00 PST Jan 26th until 00:00 PST Jan 28th. There may be brief interruption of services in that period. We apologize for the inconvenience.

×
The 365 Data Science team is proud to invite you to our own community forum. A very well built system to support your queries, questions and give the chance to show your knowledge and help others in their path of becoming Data Science specialists.
Ask
Anybody can ask a question
Answer
Anybody can answer
Vote
The best answers are voted up and moderated by our team

SQL – MySQL Triggers

SQL – MySQL Triggers

0
Votes
1
Answer

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!
 

1 Answer

365 Team
0
Votes

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