Add column to _CT table

56 views Asked by At

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.

0

There are 0 answers