I've been pondering this one myself for a few weeks on and off, having arrived at the following answer. This was specifically done to track changes within a 'logging table' for user defined tables (not every table) via stored procedures, excluding the use of external tooling requirements. I believe this is equivalent to your question. I am using TSQL for this task. Note for the below I specifically addressed INSERT actions but can be readily adjusted and expanded for UPDATE and DELETE for your own needs. I leave this exercise for the user.
I apologies in advance for the length of this response.
Given the assumption we are familiar with stored procedures we can immediately start at the guts of our work. I failed to break down my solution further than as shown, and the following and is presented with the intention of both combining each part for a complete answer (Part 1 - 5 can be copy-pasted), while explaining each part in detail for understanding.
Part 1:
BEGIN
DECLARE
@TABLE_NAME NVARCHAR(MAX) = NULL,
@PKEY NVARCHAR(MAX) = NULL,
@COLUMN_NAME NVARCHAR(MAX) = NULL,
@EventLog_I_TR NVARCHAR(MAX) = NULL
DECLARE TableCursor CURSOR FOR
SELECT TABLE_NAME FROM [dbo].[EVENT_LOG_TABLES]
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TABLE_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
In Part 1, we start with our DECLARE of required variables. To facilitate the selection of only "some" tables, I am storing the tables I want to track as string data within [EVENT_LOG_TABLES].[TABLE_NAME]. I have then used a cursor to iterate through these tables. I expect I will refine this in future to remove cursor reliance, however this has not occurred today.
Part 2:
SET @PKEY = ''
SELECT @PKEY = @PKEY + 'CAST(INSERTED.[' + COLUMN_NAME + '] AS NVARCHAR(MAX)) + '','' + '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TABLE_NAME
AND COLUMN_NAME IN (
SELECT KCU.COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
WHERE KCU.TABLE_NAME = @TABLE_NAME
AND TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
)
SET @PKEY = LEFT(@PKEY, LEN(@PKEY) - 8)
In Part 2, I have collated the primary key values of the table into a string. As we are working with many tables, which will have many different primary keys, I have chosen to concatenate these values to provide a unique and searchable value within the logging table. If all tables have the same keys, it may be more efficient to break out this section into multiple return values instead of one. I leave this decision to the user.
Part 3:
SET @EventLog_I_TR = ''
DECLARE ColumnCursor CURSOR FOR
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TABLE_NAME
AND COLUMN_NAME NOT IN (SELECT KCU.COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
WHERE KCU.TABLE_NAME = @TABLE_NAME
AND TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
)
OPEN ColumnCursor
FETCH NEXT FROM ColumnCursor INTO @COLUMN_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
I have set our trigger string (@EventLog_I_TR) to an empty string ('') so that we can concatenate this value later on (in Part 4). Meanwhile, I have also declared the cursor over the columns within the current table which are not primary keys.
Up to now, we can now assess the table (user defined), primary keys (distinct tracking), and columns (change tracking).
This leads us into Part 4:
SET @EventLog_I_TR = @EventLog_I_TR + CHAR(13) +
'INSERT INTO [dbo].[EVENT_LOG] ([TABLE], [PKEY], [COLUMN], [ACTION], [VAL]) ' +
'SELECT ''' + @TABLE_NAME + ''',' + @PKEY + ',''' + @COLUMN_NAME +
''',''I'',[' + @COLUMN_NAME + '] ' +
'FROM INSERTED WHERE ' + '[' + @COLUMN_NAME + '] IS NOT NULL; ' + CHAR(13)
FETCH NEXT FROM ColumnCursor INTO @COLUMN_NAME
END
CLOSE ColumnCursor
DEALLOCATE ColumnCursor
We set our previous @EventLog_I_TR to a concatenated SQL string where we create a series of "INSERT INTO" queries. This will insert into our [EVENT_LOG] the table, primary key, column name, action taken (insert, update, delete- In this I am inserting ("I"), and the value associated with the action.
Closing the cursor, we now have a concatenated SQL string of INSERT to handle our table data.
Part 5:
EXEC('IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N''[TR_I_' + @TABLE_NAME + ']'')) DROP TRIGGER [TR_I_' + @TABLE_NAME + ']')
EXEC('CREATE TRIGGER [TR_I_' + @TABLE_NAME + '] ON [' + @TABLE_NAME + '] AFTER INSERT AS SET NOCOUNT ON ' + @EventLog_I_TR)
FETCH NEXT FROM TableCursor INTO @TABLE_NAME
END
CLOSE TableCursor
DEALLOCATE TableCursor
END
In our final Part 5, we first drop the trigger for each table in our [EVENT_LOG_TABLES] if it exists, before recreating it as a CREATE TRIGGER "TR_I_"+@TABLE_NAME. For this example, "I" stands for 'INSERT'. Adjust as you require (or combine methods yourself for update, insert, delete).
The output result of this is a trigger on each table you have defined within [EVENT_LOG_TABLES], which AFTER INSERT will create a record within [EVENT_LOG] for the table, primary key(s), column, action, and value.
I have expanded this for my own use with timedate and user constraints, and an event_id. Apply what is most appropriate yourself.
I hope this is helpful.
