mysqldatetriggersinsert

CREATE TRIGGER ON 1st TABLE ON INSERT IN 2nd TABLE


I came across a task where whenever an employee is promoted to a manager, his/her highest salary in the latest contract must be increased by 20,000. Now, I do understand the basic idea, but in the following code:

    DELIMITER $$
CREATE TRIGGER T_update_manager_salary
AFTER INSERT ON dept_manager
FOR EACH ROW
BEGIN
    DECLARE v_salary INT;
    
SELECT 
    MAX(salary)
INTO v_salary FROM
salaries
WHERE
    emp_no = NEW.emp_no;

    IF v_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_salary + 20000, NEW.from_date, NEW.to_date);
    END IF;
END $$
DELIMITER ;

I am not able to understand why in the update salaries section, we are first equating to_date = SYSDATE() and then again in the where clause to_date = NEW.to_date? I am not able to understand what is the logic behind this. from_date and to_date both are mentiond in the dept_manager and salaries table and Insertion command is as follows :

INSERT INTO dept_manager VALUES ('11534', 'd009', DATE_FORMAT(SYSDATE(), '%Y-%m-%d'), '9999-01-01');

Solution

  • try:

    =INDEX(IFNA(REGEXEXTRACT(A1:A, "\d+ (.+)")))
    

    enter image description here