Link to home
Start Free TrialLog in
Avatar of bfuchs
bfuchsFlag for United States of America

asked on

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
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

If you are only making changes through forms, then you might take a look at my article on a simple audit log
Avatar of bfuchs

ASKER

Hi Dale,
In this particular case, changes are only done thru form calling update queries.
Thanks,
Ben
In sql server there are no forms
Dale's code is for Access
Avatar of bfuchs

ASKER

@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
Ben, why would you not use bound forms, even with a SQL Server BE?
Avatar of bfuchs

ASKER

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
Avatar of bfuchs

ASKER

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
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
Avatar of bfuchs

ASKER

Hi Dale,

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

I got my answer here...

Thanks,
Ben
ASKER CERTIFIED SOLUTION
Avatar of bfuchs
bfuchs
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial