October 11, 2005 at 5:44 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 11:28 am
1) remember that a trigger is set-based
runs 1 time per command
2)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
This could be handled by creating an unique
constraint/index on the salary table. Possibly rolling
back the whole command
2)
CREATE TRIGGER TR_PAYMENT_INSERT on P
INSTEAD OF INSERT /*instead of the original statement*/
AS
SET NOCOUNT ON /*triggers don't return records*/
INSERT INTO Payment
(/*Columnnames*/
)
SELECT /*values*/
/*needs to be an employee*/
from inserted i inner join employees e on
i.EMP_ID=e.EMP_ID
/*retrieving salary*/
inner join PAY_DURING pd on i.EMP_ID=pd.EMP_ID
/*employer matching*/
AND CURRENT_TIMESTAMP>=pd.START_DATE /*time valid*/
AND CURRENT_TIMESTAMP<pd.END_DATE
/*only when employed*/
inner join EMPLOYED_DURING ed
on i.EMP_ID=ed.EMP_ID /*employer matching*/
AND CURRENT_TIMESTAMP>=ed.START_DATE /*time valid*/
AND CURRENT_TIMESTAMP<ed.END_DATE
/*optional no 2 salaries a day*/
left join payment p on i.EMP_ID=p.EMP_ID
and p.PayTime >= /*time checking*/
CONVERT(date,CURRENT_TIMESTAMP,102)
AND and p.PayTime <
CONVERT(date,CURRENT_TIMESTAMP,102) +1
WHERE p.EMP_ID IS NULL /*optional hasn't been paid*/
Written out of my head, so there can be a syntax error
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply