why using to_date and not from_date in the last trigger example ?
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