creating triggers for temporal databases

• Ehsan Ashouri

  SSC Veteran

  Points: 241

  More actions

  October 11, 2005 at 5:43 am

  #89473

  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

• David Burrows

  SSC Guru

  Points: 65111

  More actions

  October 11, 2005 at 7:24 am

  #596524

  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.