| Author |
Topic |
|
AAAV
Posting Yak Master
152 Posts |
 Posted - 2010-03-18 : 11:34:42
|
| How to you do the auit_trail in the DB? for example if i have a user profile table withid , firstname,last name , addresline1, addressline2, city, state , zip, emailid, password, date create, date modified, created by , modified by columns....how to you track the changes to it...I am thinking about getting one table to track all the changes which is something likeTable name, type of modification(add/delete/update), modified by, modified date, updated column name, old value, new valuefor addition the updated column name will be the PK and new value will have value and old value will have null and for deletion the old value will have value and the new value will have null...Will this kind of setup work? I have a problem in this case... how do i store the other column values in case of deletion. Thanks |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-18 : 11:54:44
|
| it will work. you would need an accompanying trigger for getting values on insert/update/delete actions onto this table------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-03-18 : 12:22:49
|
| We have "parallel" tables for auditing, with two additional columns - the action (Update or Delete) and the Date/Time.I have done the "Table name / Date / Column / Old & New values" in the past and it was a nightmare by comparison. Impossible to report on, very difficult to see at-a-glance what-changed-when for a given record.My code is here:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=170215There is further discussion here:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=52356#176556(that thread also discusses the Table/Date/Column/Old/New values approach too)and a newer post, with some additional thinking/suggestions here:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=139702#546701 |
|
|
|
AAAV
Posting Yak Master
152 Posts |
Posted - 2010-03-18 : 13:04:40
|
| Thanks for the reply... i am thinking about the parallel tables mostly but can you explain "I usually add 5 columns to my auditing tables to capture: SYSTEM_USER, host_name(), APP_NAME(), user_name(), getdate()"Why do we need 5 coulumns i was thinking the Action and Audit_date isenough? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-03-18 : 13:23:55
|
| I think it was Russell that does that?Our users login, and we store the UserID + DateTime in the record itself (CreateDate/User and UpdateDate/User) - so that, in turn, gets copied into the Archive table, so we don't need those.But for auditing purposes (e.g. fraud / hacking diagnosis after-the-fact) they might be helpful |
|
|
|
AAAV
Posting Yak Master
152 Posts |
Posted - 2010-03-18 : 13:38:52
|
| I have created by and updated by in the main table...SYSTEM_USER is the same as updated by i guess.What is the reason for host_name(), APP_NAME() |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-18 : 13:41:31
|
quote:
Originally posted by AAAV
I have created by and updated by in the main table...SYSTEM_USER is the same as updated by i guess.What is the reason for host_name(), APP_NAME()
to get details of application that caused a particular change and host machine------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-03-18 : 13:43:28
|
(They are always the same for me as ours is a web app - one application, one host  ) |
|
|
|
AAAV
Posting Yak Master
152 Posts |
Posted - 2010-03-18 : 13:46:04
|
| Same for me too i guess. i have one web application, though user and back office people can change it.It is through only one application. I will leave them... |
|
|
|
AAAV
Posting Yak Master
152 Posts |
Posted - 2010-03-18 : 13:46:22
|
| Thanks for your time and reply |
|
|
|
|
History