Trigger to write to history table

My PREFERRED method would be to have a table that models the fields in the source table and to log the changes in column format.  I am searching for it, but I gave a few examples not long ago on how to do this each way... stay tuned

What would you recommend if you are trying to track changes made in more than one table?  Make multiple history tables?  I think this is one of the reasons I am going with a history of one table only.

What kind of performance hit is it going to be?  More than a second or two?

I'll try the trigger sometime today or tomorrow.  Thanks for the input.

Well you have to write individual insert statements to write one record per column changed.  As opposed to one per insert/update.

I personally have used the method of one history table per table the most.  It gives you the ability to easily do effective dating of records.  

So let's say you have a table with 4 columns.  a, b, c and d for simplicity.  With a being your primary key.  And the table is called Tab1.  I would create a table called Tab1_History, add a surrogate key Tab1_History_Id as an identity.  Add an effective_Start_Dttm and Effective_End_Dttm.

create trigger t
on tab1 for insert, update
as
declare @proc_Dttm datetime
 
set @proc_Dttm = getdate() -- or getUTCDate() if you do UTC time
insert into Tab1_History(a,b,c,d,Effective_Start_Dttm)
select a,b,c,d,@proc_Dttm from inserted
 
update t1h
set Effective_End_Dttm = @proc_Dttm
from tab1_history t1h
join inserted i
on t1h.a = i.a
where th1.effective_Start_Dttm <> @proc_Dttm
and effective_end_dttm is null
 
return
GO
 
-- what this effectively accomplishes is you know for how long
-- a particular set of values existed in a table.  You could
-- go back and say... what did this table look like on 2/12/2008 
-- at 10 am EASILY by writing the following query
 
 
select * from tab1_history
where effective_start_Dttm <= '2/12/2008 10am'
and (effective_end_dttm > '2/12/2008 10am' or effective_end_dttm is null)

Select allOpen in new window

OK I got this one to work although I changed the select i.'s to select d.'s

One last thing.  what if I want to username to the history table.  Is that a simple adjustment on the insert and select statements?

cREATE TRIGGER trgEdit ON tblEmpInfo
FOR  UPDATE
AS
if @@rowcount=0
  return
 
insert tblEMSHistory (EmpId, Field,NewValue)
select d.empId,'EmpStatus', d.EmpStatus
from inserted i join deleted d on i.empID = d.empID
and i.empStatus <> d.empStatus

Select allOpen in new window

you want to record the old value, not the new value?

Yes I did.   Sorry if I wasn't clear about that.  but the trigger does work nicely

Thanks again.  I used the first example here.