Reports

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.

79401860