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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy