Firstly,
Change data capture is available only on the Enterprise, Developer,
and Evaluation editions of SQL Server.
So that may decide for you if any of your customers will not have the enterprise editions, or you don't yet know you will be using the enterprise editions. (As the spec includes "multiple future applications" this may be an real issue for you)
Unlike triggers it is not real time, this is both an advantage and a disadvantage. Using triggers always slow down an update.
I worked on one system when we used triggers (generated by CodeSmith), as well as tracking all the changes to the records, we also linked the changes together to a “history” table that included the module of the application that made the change, and the UI item the user used to make the change.
However you may be best solving this at the application level, by say writing all update to a message queue that is then replayed to create a database at any given point of time, see Temporal Patterns on Martin Flowler blog for a good overview of options.
I think you would need to use a trigger on the table(s) in question in order to get the table name.
How I have implemented table change tracking is by just that: 1) Create a table to hold the changes and 2) Create a trigger on each table that needed to be tracked.
The triggers ALL look like this (where you replace 'XX_YOURTABLE_XX' with the table in question and [tr_XXXX] with a unique trigger name ):
CREATE TRIGGER [dbo].[tr_XXXX] on [dbo].[XX_YOURTABLE_XX] for INSERT, UPDATE, DELETE
AS
DECLARE
@bit INT ,
@field INT ,
@maxfield INT ,
@char INT ,
@fieldname VARCHAR(128) ,
@TableName VARCHAR(128) ,
@PKCols VARCHAR(1000) ,
@sql VARCHAR(2000),
@UserName VARCHAR(128) ,
@Type char(1) ,
@PKSELECT VARCHAR(1000)
SELECT @TableName = 'XX_YOURTABLE_XX'
-- Get User
IF object_id('tempdb..#TmpUser') IS NOT NULL
SELECT @UserName = TheUser FROM #TmpUser
ELSE
SELECT @UserName = system_user
-- Action
IF EXISTS (SELECT * FROM INSERTED)
IF EXISTS (SELECT * FROM DELETED)
SELECT @Type = 'U' --UPDATE
ELSE
SELECT @Type = 'I' --INSERT
ELSE
SELECT @Type = 'D' --DELETE
-- get lISt of columns
SELECT * INTo #ins FROM INSERTED
SELECT * INTo #del FROM DELETED
-- Get primary key columns for full outer join
SELECT @PKCols = coalesce(@PKCols + ' AND', ' on') + ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
WHERE pk.TABLE_NAME = @TableName
AND CONSTRAINT_TYPE = 'PRIMARY KEY'
AND c.TABLE_NAME = pk.TABLE_NAME
AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
-- Get primary key SELECT for INSERT
SELECT @PKSELECT = coalesce(@PKSELECT+'+','') + '''<' + COLUMN_NAME + '=''+convert(VARCHAR(100),coalesce(i.' + COLUMN_NAME +',d.' + COLUMN_NAME + '))+''>'''
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
WHERE pk.TABLE_NAME = @TableName
AND CONSTRAINT_TYPE = 'PRIMARY KEY'
AND c.TABLE_NAME = pk.TABLE_NAME
AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
IF @PKCols IS NULL
BEGIN
RAISERROR('no PK on table %s', 16, -1, @TableName)
RETURN
END
SELECT @field = 0, @maxfield = max(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName
WHILE @field < @maxfield
BEGIN
SELECT @field = min(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND ORDINAL_POSITION > @field
SELECT @bit = (@field - 1 )% 8 + 1
SELECT @bit = power(2,@bit - 1)
SELECT @char = ((@field - 1) / 8) + 1
IF substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in ('I','D')
BEGIN
SELECT @fieldname = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND ORDINAL_POSITION = @field
SELECT @sql = 'INSERT tbl_TRACKING (Type, TableName, PK, FieldName, OldValue, NewValue, UserName)'
SELECT @sql = @sql + ' SELECT ''' + @Type + ''''
SELECT @sql = @sql + ',''' + @TableName + ''''
SELECT @sql = @sql + ',' + REPLACE(REPLACE(REPLACE(@PKSELECT,'<',''),'>',''),'PriKeyVal=','')
SELECT @sql = @sql + ',''' + @fieldname + ''''
SELECT @sql = @sql + ',convert(VARCHAR(1000),d.' + @fieldname + ')'
SELECT @sql = @sql + ',convert(VARCHAR(1000),i.' + @fieldname + ')'
SELECT @sql = @sql + ',''' + @UserName + ''''
SELECT @sql = @sql + ' FROM #ins i full outer join #del d'
SELECT @sql = @sql + @PKCols
SELECT @sql = @sql + ' WHERE i.' + @fieldname + ' <> d.' + @fieldname
SELECT @sql = @sql + ' or (i.' + @fieldname + ' IS NULL AND d.' + @fieldname + ' IS not NULL)'
SELECT @sql = @sql + ' or (i.' + @fieldname + ' IS not NULL AND d.' + @fieldname + ' IS NULL)'
exec (@sql)
END
END
GO
The tracking table looks like this:
CREATE TABLE [dbo].[tbl_TRACKING](
[Type] [char](1) NULL,
[TableName] [varchar](128) NULL,
[PK] [varchar](1000) NULL,
[FieldName] [varchar](128) NULL,
[OldValue] [varchar](1000) NULL,
[NewValue] [varchar](1000) NULL,
[ActionDate] [datetimeoffset](3) NULL,
[UserName] [varchar](128) NULL,
[AppName] [varchar](128) NULL,
[ComputerName] [varchar](128) NULL
)
Hope that helps
Best Answer
Temporal tables are new and in all editions. They add a start and end time to rows and keeps a history of datachanges with some DDL to pull back changes.
It may be possible to stick into existing applications but has some limitations (eg no cascade deletes) and so is much more likely to be put in during design time so that developers don't need to roll their own anymore.