October 11, 2005 at 5:43 am
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
October 11, 2005 at 7:24 am
1. Your code indicates that PAY_DURING and EMPLOYED_DURING records exist before the EMPLOYEE record
2. Write a sp to insert EMPLOYEE and do the checks there
3. If sp not possible and assuming your on SQL2K then write an 'INSTEAD OF' trigger which will avoid the need to DELETE
4. Using a trigger means you have to check for more than one row 'INSERTED' and for possibly more then 1 EMPLOYEE (better to use sp as above)
5. When inserting EMPLOYEE where are the dates?
Can you describe the process a bit more with sample data and expected output?
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply