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.
| Author |
Topic |
|
DBADave
Constraint Violating Yak Guru
366 Posts |
 Posted - 2007-01-23 : 10:41:52
|
| What's your opinion on requiring certain attributes in tables such as CreateDate, CreateUser, ModifyDate, ModifyUser? I know there are situations where this does not make sense such as fairly static lookup tables, however there are also times where it would be nice to track who created or changed a record and when the change occurred. Just curious how you approach this issue with your designs?Thanks, Dave |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2007-01-24 : 04:48:41
|
| Search for info from member "NR" on his website about "audit logging". His solution seems to be simple and workable. |
 |
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2007-01-24 : 11:03:49
|
| Unfortunately I have very little luck with the search engine on this forum. Most of the time I receive the following message.Microsoft OLE DB Provider for SQL Server error '80040e31' Timeout expired /forums/search.asp, line 423 Do you recall the where in the forum I can find this such as under SQL Server Administration?Thanks, Dave |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-01-24 : 11:30:53
|
http://www.nigelrivett.net/SQLTriggers/Triggers_2_Creating_Audit_Trails.htmlGo with the flow & have fun! Else fight the flow  blog thingie: http://weblogs.sqlteam.com/mladenp |
|
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2007-01-24 : 11:40:41
|
| Thanks |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-01-24 : 12:46:20
|
| We have columns for Create User & Date, and also for Modify User/Date, in pretty much all our tables.We also have an Audit table (which stores the "before" for all Updates / Deletions) matching most of our tables.We purge stale data from the Audit table - usually leaving the most recent Audit record (for a given PK) if all Audit records are older than the cutoff - so the remaining record indicates at least one previous change.We don't store the Current value in the Audit table, to save space, as it is already in the Main table.The Audit Table data is maintained by Triggers.See: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=audit%20changes"Timeout expired"Yup ... try "The SQL Search" at the top of the page. It uses Google to search relevant SQL sites - including this one I believeKristen |
|
|
|
|
|
|
Opinion? - Requiring CreateDate & CreateUser