As a test, I enabled CDC on a DB and table for specific columns.
CREATE TABLE [dbo].[testCDC]
(
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[Col1] [nchar](10) NULL,
[Col2] [nchar](10) NULL,
CONSTRAINT [PK_testCDC]
PRIMARY KEY CLUSTERED ([ID] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
The corresponding CDC table looks like this:
[__$start_lsn], [__$end_lsn], [__$seqval], [__$operation], [__$update_mask],
[ID], [Col1], [Col2], [__$command_id]
One of our requirements is to not only capture the changed data but also the person that conducted that change. (SYSTEM_USER)
Is there any way how I can manipulate the CDC mechanism that for each table, although that table does not have a LastModifiedByUser column, such a column appears in the _CT table?
If not, a workaround would be to add a new column to the original table, define a default constraint and an update trigger on the table. That way, each time a user makes a change, the data is captured incl. the user that made the change but in 4 rows instead of 2 in the _CT table.