November 19, 2013 at 7:27 am
My latest mission at work - well, one of them, is...
I have two tables - Suppliers and Supplier Addresses.
I need to record all changes to these tables. What the change was, who made it, when, etc.
Looooooong time ago - 10 years - I had to do something similar in Informix. I created a "new" table then had a trigger insert into that table from the application table whenever it was modified.
Now I'm on SQL Server 2008 R2 I'm looking for advice on the best practice and/or the easiest way to accomplish this.
I should add that although I'm in and out of SQL daily I'm a one-man-band IT guy so "Do it Like This" for dummies would be a great help!
November 19, 2013 at 7:41 am
Malcolm, triggers are probably the right way to bolt on this type of behavior. Here is one example (I have not tried) of a generator http://gallery.technet.microsoft.com/scriptcenter/Create-Audit-Table-and-5cd69d5d instead of doing them all manually.
November 19, 2013 at 8:06 am
Depends on how long you want to keep the information for 🙂
If it's only for a short time to allow export to a reporting database, you could use Change Data Capture:
http://technet.microsoft.com/en-us/library/bb522489(v=sql.105).aspx
..or perhaps even SQL Server Audit, an extract is here (and it's available as a free eBook - what's not to love?):
http://bradmcgehee.com/2010/03/30/an-introduction-to-sql-server-2008-audit/
I've seen trigger based approaches used very successfully in the past, though, so don't let that put you off creating your own 🙂
[font="Courier New"]sqlmunkee[/font]
[font="Courier New"]Bringing joy and happiness via SQL Server since 1998[/font]
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply