creating rules for temporal databases

• Ehsan Ashouri

  SSC Veteran

  Points: 241

  More actions

  October 11, 2005 at 5:44 am

  #166243

   

  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

• Jo Pattyn

  SSC-Dedicated

  Points: 32424

  More actions

  October 11, 2005 at 11:28 am

  #596595

  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