>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

create trigger trInfo_Update ON Info AFTER UPDATE
AS
   BEGIN
    SET NOCOUNT ON;
    insert into Audit (Name, From, Thru)
    select D.Name, D.From, dateadd(day,-1, CAST(CURRENT_TIMESTAMP as DATE)) from Deleted D -- original row
    UNION ALL
    select I.Name, CAST(CURRENT_TIMESTAMP as DATE), I.Thru from Inserted I
 END

If I understood your idea right.