• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 303
  • Last Modified:

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?
  • 4
  • 4
1 Solution
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
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.
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now