Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Audit tables and their formats?

Author  Topic 

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2014-07-28 : 13:04:21
Hi friends,

I am designing an inventory database and would like to have an audit trail for one of its table which would be handled by users via website.
at the end of the day i would be using the audit table for reporting .

There are many ways of doing it but i want to maintain the audit table in such a way that reporting is made easier when i have to check certain columns for changes and all.

Which kind of auditing table format is better.

This is what i have..The table has new record for any field update on the original.Is this a good way ..Thank You:
[AuditId]
,[TransId]
,[Eq_number]
,[ProjectID]
,[ProjectOfficeId]
,[CheckedOutBy]
,[CheckOutDate]
,[DateReturned]
,[Notes]
,[Trans_StatusId]
,[AuditAction]
,[AuditDate]
,[User_Login]
,[AuditApp]


gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-07-29 : 11:58:09
Shouldn't you include the name of the field that was changed and its old and new contents?
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-07-29 : 12:07:17
I'm assuming that the audit table has all the columns from the base table plus audit columns? If so, then that should work. The other thing you need to determine is if the audit table will only have history or if it will have history and the current row.
Go to Top of Page
   

- Advertisement -