Trigger to write to history table

I would like to write a trigger for 5 fields in a table.  If any or all of those 5 fields data changing I would like to write that data into a history table.  History table looks like this:

fieldname is the name of one of the five fields that the data will change...and fielddata is the changed data...and DATEMOD is the date the data was changed.

One other question.  Will this trigger change depending on version of SQLServer 2005 or 2000?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
wellsjAuthor Commented:
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.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
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
-- 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)

Open in new window

wellsjAuthor Commented:
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
if @@rowcount=0
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

Open in new window

you want to record the old value, not the new value?
wellsjAuthor Commented:
Yes I did.   Sorry if I wasn't clear about that.  but the trigger does work nicely
wellsjAuthor Commented:
Thanks again.  I used the first example here.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.