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