Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004

    Unanswered: table with record history eff_from & eff_to

    Hi guys,

    here is my problem. I made a database with few tables. Let's have a look at table t_users:
    fields: user_id INT, eff_from TIMESTAMP, eff_to TIMESTAMP

    Some example data:
    1 01.01.2005 12:00 01.01.2006 11:59
    1 01.01.2006 12:00 NULL ...
    2 01.02.2006 12:00 NULL ...

    I want to keep history of all records. The active records is where eff_to IS NULL. My problem is that I want to have a trigger which will close the active records (setting eff_to = current_timestamp) and insert new one. Running UPDATE in trigger BEFORE/AFTER UPDATE cause a loop, etc.

    I couldn't write the trigger by my own. Can anyone give me advice, solution for my problem or at least tell me which event I should trigger?

  2. #2
    Join Date
    Jan 2004
    I forgot to mention that the primary key is (user_id, eff_from)...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts