Last answered:

16 Jul 2021

Posted on:

16 Jul 2021

0

SYSDATE Function does not exist

Calling the SYSDATE() function as per lecture however returns this function does not exist. Also have tried NOW() and this also returns the same error.

'Error Code: 1305. FUNCTION employees.SYSTDATE does not exist'

USE employees; 

DELIMITER $$

CREATE TRIGGER trig_ins_dept_mng
AFTER INSERT ON dept_manager 
FOR EACH ROW
	BEGIN
		DECLARE v_curr_salary int; # create a variable to contain current salary
			SELECT MAX(salary)
            INTO v_curr_salary    # insert max salary to variable
            FROM salaries 
            WHERE emp_no = NEW.emp_no;  # only run when a change is made to emp_no
				IF v_curr_salary IS NOT NULL THEN
					UPDATE salaries
                    SET to_date = SYSTDATE()
                    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 ;

INSERT INTO dept_manager VALUES ('111534', 'd009', date_format(sysdate(),'%Y-%m-%d'), '9999-01-01');

SELECT * FROM dept_manager WHERE emp_no = '111534';
1 answers ( 0 marked as helpful)
Instructor
Posted on:

16 Jul 2021

0

Hi Thomas!

Great to have you in the course and thanks for reaching out!

The function's name is still deriving from System Date, however it is just SYSDATE(), not SYSTDATE().
Please remove the letter T and retry.

Hope this helps.
Best,
Martin

Submit an answer