given the following schema
EMPLOYEE(EMP_ID, NAME, SALARY)
PAY_DURING(EMP_ID, SALARY, START_DATE, END_DATE)
EMPLOYED_DURING(EMP_ID, NAME, START_DATE, END_DATE)
how would you implement a trigger that stops the same employee from having the two salaries on the same day...so far I have this
---------------------------------------
Create Rule 2_salaries_same_day on EMPLOYEE
When INSERTED
IF EXISTS(SELECT * FROM INSERTED
WHERE
(SELECT * FROM PAY_DURING WHERE EMP_ID = PAY_DURING.EMP_ID
AND
START_DATE => PAY_DURING.EMP_ID
AND/OR
END_DATE <= PAY_DURING.EMP_ID))
THEN DELETE....................................
so far I have this but I'm not sure how to go about the deletion and whether there is a way of recursive deletion.
I'm also having trouble implementing a rule that stops an employee from having a salary outside his period of employment.
HELP ME!!!!!!!!!!!!!!!!!!!!! Cheer Ehsan