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 |
|
trondaron
Starting Member
13 Posts |
 Posted - 2009-06-15 : 20:05:03
|
| I'm considering a table to store changes made to any table/row in our database. Currently it looks like this:CREATE TABLE dbo.rowChangeLog ( changeID int NOT NULL IDENTITY (1, 1), stamp smalldatetime NOT NULL, userID int NOT NULL, tableName varchar(100) NOT NULL, rowData varbinary(7900) NOT NULL ) ON [PRIMARY]Basic idea is when a row is changed to covnert existing row data to a binary format (or some other generic format) and store it here.I'm also considering a database version number field so after updates rowData can be reconstructed according to the database schema at the time it was created.Problems/Suggestions/Thoughts? |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2009-06-16 : 07:32:00
|
| Have a look athttp://www.nigelrivett.net/SQLTriggers/AuditTrailTrigger.htmlIt is a generic trigger to audit trail each changed column in a table.Would be easy to convert to your structure.There are other triggers there to audit the whole row too.I probably wouldn't use your format as it would be a bit unweildy. I either have an audit table for each table or a single table and audit column changes.Consider varbinary(max) rather than 7900==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2009-06-16 : 10:55:17
|
| You'd find it much more useful to create separate audit tables for each of your production tables, rather than the monstrosity you are going to get with a single table recording a new record for every column changed in every table.________________________________________________If it is not practically useful, then it is practically useless.________________________________________________ |
|
|
|
trondaron
Starting Member
13 Posts |
Posted - 2009-06-16 : 20:41:53
|
| Suggestions noted. Thank you!If two people tell you that you're drunk, it's probably time to take a nap.On an additional note, would using Sql Server 2008's Change Data Capture be inappropriate? |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2009-06-17 : 11:44:18
|
| Haven't tried it, but would probably prefer my personal "roll-your-own" solution using archive tables: http://sqlblindman.pastebin.com/m5dc8e43________________________________________________If it is not practically useful, then it is practically useless.________________________________________________ |
|
|
|
|
|
|
Row Change Log