create table foobar (
   foo int primary key,
   bar varchar(5),
)

create table foobar_hist (
   foo int,
   bar varchar(5),
   change  char(1),   -- Type of change
   changed datetime,  -- when change occurred
   constraint PK_foobar_hist primary key (foo, changed)
)

/* MS-SQL triggers provide two virtual tables:
 *   INSERTED - holds records to add to the table, and 
 *   DELETED - holds records to delete from the table
 * An update is treated as an insert and delete at the
 * same time, so you have access to the before *and*
 * after values of the columns.
 */
create trigger foobar_ins on foobar for insert as
   insert foobar_hist (foo, bar, change, changed)
   select foo, bar, 'I', getdate()
   from INSERTED

create trigger foobar_upd on foobar for update as
   insert foobar_hist (foo, bar, change, changed)
   select foo, bar, 'U', getdate()
   from INSERTED

create trigger foobar_del on foobar for delete as
   -- Note: Final record is duplicated in history table the
   -- last I or U record and this D record have the same
   -- values other than the change/changed columns.
   insert foobar_hist (foo, bar, change, changed)
   select foo, bar, 'D', getdate()
   from DELETED