Unanswered: table with record history eff_from & eff_to
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?