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; at this example if we wanted the current date to be the from_date at the salaries table shouldn't it be from_date=SYSDATE ?

Hi Toka!Thanks for reaching out. I can understand your confusion but if you look at the next insert statement provided in the .sql file, you will understand why this would work. We are providing the date '9999-01-01' as to_date which will coincide with the current to_date and the trigger will work just fine. We would have an issue if the insert statement had another value for to_date. To avoid this we might have altered the trigger a bit: ...UPDATE salaries SET to_date = SYSDATE()WHERE emp_no = NEW.emp_no and to_date = ( SELECT MAX(to_date) FROM salaries WHERE emp_no = NEW.emp_no);...Now we would have the latest record every time and we would update it each time an insert with arbitrary to_date was executed. Hope this helps.Best,Tsvetelin

Follow this topic

Toka Saad

Last answered:

20 Feb 2023

Posted on:

19 Feb 2023

why using to_date and not from_date in the last trigger example ?

in SQL / MySQL Triggers

1 answers ( 0 marked as helpful)

Tsvetelin Tsonkov

Instructor

Posted on:

20 Feb 2023

why using to_date and not from_date in the last trigger example ?

Submit an answer

related questions