There's an hidden gem in SQL Server 2008: Change Data Capture (CDC). Using CDC we get full audit capabilities with absolutely no implementation code: we can see all changes made to a specific table, including the old and new values! You can only use CDC in SQL Server 2008 Standard or Enterprise, Express edition is not supported. Here are the steps you need to take, just remember SQL Agent must be running:
use SomeDatabase;
-- first create a table
CREATE TABLE
Author
(
ID INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
Name NVARCHAR(20) NOT NULL,
EMail NVARCHAR(50) NOT NULL,
Birthday DATE NOT NULL
)
-- enable CDC at the DB level
EXEC sys.sp_cdc_enable_db
-- check CDC is enabled for the current DB
SELECT name, is_cdc_enabled
FROM sys.databases
WHERE name = 'SomeDatabase'
-- enable CDC for table
Author, all columns
exec sys.sp_cdc_enable_table @source_schema = 'dbo', @source_name = '
Author', @role_name = null
-- insert values into table
Author
insert into
Author (Name, EMail, Birthday, Username)
values ('Bla', 'bla@bla', 1990-10-10, 'bla')
-- check CDC data for table
Author
-- __$operation: 1 = DELETE, 2 = INSERT, 3 = BEFORE UPDATE 4 = AFTER UPDATE
-- __$start_lsn: operation timestamp
select *
from cdc.dbo_author_CT
-- update table
Author
update
Author
set EMail = '
[email protected]'
where Name = 'Bla'
-- check CDC data for table
Author
select *
from cdc.dbo_author_CT
-- delete from table
Author
delete from
Author
-- check CDC data for table
Author
select *
from cdc.dbo_author_CT
-- disable CDC for table
Author
-- this removes all CDC data, so be carefull
exec sys.sp_cdc_disable_table @source_schema = 'dbo', @source_name = '
Author', @capture_instance = 'dbo_Author'
-- disable CDC for the entire DB
-- this removes all CDC data, so be carefull
exec sys.sp_cdc_disable_db
SyntaxHighlighter.config.clipboardSwf = 'http://alexgorbatchev.com/pub/sh/2.0.320/scripts/clipboard.swf';
SyntaxHighlighter.all();
