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();
