I have a number of tables that have from_date / thru_date to indicate currently active row. Rows in this table are never deleted or changed but in the language of the business they are "thru-dated" i.e. the old row gets a thru_date of today and a new row is created with a from_date of tomorrow.
( thru_date otherwise defaults to '20500101' )
I would like to be able to send an Update from my C# program to the back end and have an UpdateInsteadofTrigger change the current row replacing the thru_date with whatever is in the update as the from_date -1 ( sometimes the "thru_dating" is in the future )
The exisiting row to be updated :
Joe Johnson 20080909 20500101
and I would like to change Johnson to Jackson, the from_date to 20120129, send the update and get two rows
Joe Johnson 20080909 20120128 // updated original row
Joe Jackson 20120129 20500101 // new row
as if by magic ( or Trigger Elves ) .
I'm always in over my head in T SQL so any spoon-feeding guidance will be greatly appreciated.
TIA
Charles Hankey
Though a good deal is too strange to be believed, nothing is too strange to have happened.
- Thomas Hardy
Half the harm that is done in this world is due to people who want to feel important. They don't mean to do harm-- but the harm does not interest them. Or they do not see it, or they justify it because they are absorbed in the endless struggle to think well of themselves.
-- T. S. Eliot
Democracy is two wolves and a sheep voting on what to have for lunch.
Liberty is a well-armed sheep contesting the vote.
- Ben Franklin
Pardon him, Theodotus. He is a barbarian, and thinks that the customs of his tribe and island are the laws of nature.
