In one of my application Iam using SQL SERVER 2008, I like to implement audit tables for few tables. One option I have to create triggers on the respective tables. Can anyone suggest me any other good, robust and secure option.
+1
A:
SQL Server 2008 has a new auditing feature: Auditing in SQL Server 2008.
There is also Change Data Capture
Mitch Wheat
2010-02-09 11:17:22
Oops. You beat me to it. I'm a slow typer... :-)
Frank Kalis
2010-02-09 11:20:53
+1 but with the caveat that this depends on edition of SQL Server 2008 being used. CDC requires Enterprise, Developer or Evaluation and I believe auditing requires Enterprise too?
AdaTheDev
2010-02-09 11:21:22
@AdaTheDev: Thanks, I'd completely forgotten about the edition.
Mitch Wheat
2010-02-09 11:46:33
A:
Triggers are the usual way to go. Another option could be to handle this in your stored procedures. Of course this assumes, that all data modification goes via stored procedure. Since you are on SQL Server 2008 and assuming you don't care about any lower version, check out the new auditing features here: http://msdn.microsoft.com/en-us/library/dd392015.aspx
Frank Kalis
2010-02-09 11:20:15
A:
You don't mentione which edition of SQL Server you are using as the Auditing features are only available in the Enterprise edition.
You should download a copy of the free eBook Developing Time-Oriented Database Applications in SQL by Richard T. Snodgrass from here: http://www.cs.arizona.edu/people/rts/tdbbook.pdf.
He talks about auditing and many other time related issues in databases.
Tony
2010-02-09 11:26:39