We help IT Professionals succeed at work.

Looking for code to save changes into log table.

Hi Experts,

I am looking to create a log table that will keep history of all changes of a SQL table.

Guess the most accurate way of accomplishing this it is thru a trigger.

Wondering if someone has written such a general trigger that will loop thru all fields and save changes to the log table?

Thanks
Comment
Watch Question

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
If you are only making changes through forms, then you might take a look at my article on a simple audit log
Hi Dale,
In this particular case, changes are only done thru form calling update queries.
Thanks,
Ben
Partha MandayamTechnical Director

Commented:
In sql server there are no forms
Dale's code is for Access
@Partha,
Correct, It happened to be that I have an Access application linked to this SQL table, however I'm basically looking for a SQL solution as the changes are not done using regular bound forms.
Thanks,
Ben
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Ben, why would you not use bound forms, even with a SQL Server BE?
Hi Dale,

why would you not use bound forms

The data changes are done in Caspio, I just have them imported into an SQL/Access app for reporting, and after printing users are presented with a question like printed/reviewed etc...and this update applies to an entire selection...

However at the moment I'm happy if I can get the T-SQL code to create just a simple trigger to keep track of one table, one field.

Will expend later as needed.

Thanks,
Ben
Hi Dale,

The question is very simple (in my eyes..), I need a trigger that will fire after update of particular table (Skilled_Nursing_Visit_Notes), check if visit date field of inserted is different from previous then insert a row in log table.
I just need help in t-SQL syntax…

Thanks,
Ben
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
I'm not your guy, Ben.  I don't do much with SQL Triggers

Have you done a search on "T-SQL Audit"?

I found a bunch of questions which are similar to yours, one of them might have the solution to your issue.

Dale
Hi Dale,

I'm not your guy, Ben.
You're, just not for this thread-:)

I got my answer here...

Thanks,
Ben
As mentioned, the link above contains the answer.