Tony Rogerson's ramblings on SQL Server

When holding history in the same table there are times you need to 'end date' or deactivate the current row thereby making your history row.

This is difficult to do unless you are using a surrogate key that is based around an incremental counter for example using the IDENTITY property.

Here is an example of how to code it....

create table update_preceeding_row (
   id int not null identity constraint sk_update_preceeding_row primary key clustered,
   somedata int not null,
   closed_yn char(1) null check( closed_yn in ( 'Y', 'N' ) ),
   closed_datetime datetime null
)
go

create trigger trg_update_preceeding_row_close_previous_row
         on update_preceeding_row for insert
as
begin

   if @@rowcount = 0
      return

   if @@rowcount > 1
   begin
      print 'Trigger only works for one row inserts'
      rollback tran
   end

   update bt
      set closed_yn = 'Y',
            closed_datetime = getdate()
   from update_preceeding_row as bt
   where bt.id = (
            select max( bt2.id )
            from inserted i
                     inner join update_preceeding_row bt2 on bt2.id < i.id
         )

end
go

insert update_preceeding_row ( somedata ) values( 1 )
select *
from update_preceeding_row

insert update_preceeding_row ( somedata ) values( 2 )
select *
from update_preceeding_row

insert update_preceeding_row ( somedata ) values( 3 )
select *
from update_preceeding_row

drop table update_preceeding_row

How does this work? Lets look at the statement that is really doing the work...

   from update_preceeding_row as bt
   where bt.id = (
            select max( bt2.id )
            from inserted i
                     inner join update_preceeding_row bt2 on bt2.id < i.id
         )

For each row from the inserted table (a table materialised by SQL Server and contains the rows you are inserting or for an update the new values for the rows you are updating) we join to the base table and work out the preceeding ID, that is the row whose ID is less than our current working row when reading through the inserted table. We then pin point the row to update by joining back into the base table.

This technique only works because you know the order the rows where inserted, you could also use a datetime entry_date type column, but be wary, you may have duplicate rows and in which case you will probably get multiple rows in update_preceeding_row updated.

Note, I've updated this since first posting because David Portas correctly pointed out that an insert of multiple rows causes many rows to become active and also for many inserted rows how do you know which is the correct active row! May be in the future when I've time I'll revist the logic...