I'm working on implementing Change Data Tracking in a SQL Server database and I set up a single table to test changes. In the past, changes have been made manually, but I'd like to create a way to track updates and deletions automatically through the built-in CDC process. In many cases, these changes are spread across dozens of different tables, so the ideal situation would be to create a view that collocates all of the changed data.
CDC works at the table level and doesn't track the user who makes changes. I found a way to add user data to the CDC tracking tables by creating a new user_name column and adding SUSER_SNAME() to the "Default Value or Binding" column property using the SQL command: "ALTER TABLE cdc.dbo_MyTable_CT ADD UserName nvarchar(50) NULL DEFAULT(SUSER_SNAME())" from a separate Stack Overflow question – (https://stackoverflow.com/questions/869300/sql-server-2008-change-data-capture-who-made-the-change).
I'm trying to find a similar stored function to track the table name from this list – https://msdn.microsoft.com/en-us/library/ms187812.aspx.
So far the only stored function I've successfully implemented from the list is db_name() which automatically returns the database name. I've tried schema_name and object_name, but those automatically revert to N'schema_name()' or N'object_name()' in the design view. In the table view, the changed rows return (schema_name()) or (object_name()).
Does anyone know how to automatically populate the a column with the table name of the table from which changes were made using some sort of stored function?
Best Answer
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 ):
The tracking table looks like this:
Hope that helps