SQL Server: log database changes through generic trigger

Starting from this article, which is creating a trigger to log insert-, update- and delete-statements within the database, I’d like to create a similar trigger which doesn’t require to define the table and its columns. This would us to reduce human errors when dropping or adding columns.

I’ve had many issues (hence the extra code compared to the article) but wasn’t able to overcome accessing the table columns through a stringified name within the trigger.

-- Create the ChangeLog table
CREATE TABLE ChangeLog
(
    ID BIGINT PRIMARY KEY IDENTITY(1,1) NOT NULL,
    COMMAND NCHAR(6) NOT NULL,
    CHANGED_DATE DATETIME2 DEFAULT GETDATE() NOT NULL,
    TABLE_NAME NVARCHAR(255) NOT NULL,
    COLUMN_NAMES TEXT NULL,
    COLUMN_OLD_VALUES TEXT NULL,
    COLUMN_NEW_VALUES TEXT NULL,
    USERNAME NVARCHAR(100) NOT NULL
)
GO

-- Create Trigger for Table to log changes
ALTER TRIGGER CHANGE_MyTableName
ON MyTableName
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    -- Define which command was executed
    DECLARE @command CHAR(6)
    SET @command =
    CASE
        WHEN EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted) THEN 'UPDATE'
        WHEN EXISTS(SELECT * FROM inserted) THEN 'INSERT'
        WHEN EXISTS(SELECT * FROM deleted) THEN 'DELETE'
        ELSE NULL
    END

    -- Define variables
    DECLARE @seperator NVARCHAR(2)
    SET @seperator = ', '

    DECLARE @column_name NVARCHAR(255)
    DECLARE @column_names VARCHAR(MAX)
    DECLARE @column_old_values VARCHAR(MAX)
    DECLARE @column_new_values VARCHAR(MAX)

    -- Select the column names to populate @column_names separated by ', '
    SELECT @column_names = COALESCE(@column_names + @seperator, '') + COLUMN_NAME 
    FROM information_schema.columns 
    WHERE table_name = 'MyTableName'

    -- Create cursor to populate @column_old_values or/and @column_new_values
    DECLARE CURSOR_FOR_COLUMN_NAMES CURSOR FOR 
        -- Select the column name as a string
        SELECT sys.columns.name AS ColumnName
        FROM sys.columns JOIN sys.tables ON sys.columns.object_id = tables.object_id
        WHERE tables.name = 'MyTableName'

    -- Perform the first fetch.
    OPEN CURSOR_FOR_COLUMN_NAMES 

    FETCH NEXT FROM CURSOR_FOR_COLUMN_NAMES INTO @column_name

    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @command = 'UPDATE' or @command = 'DELETE'
            -- Select the old values to populate @column_old_values separated by ', '
            SELECT @column_old_values = COALESCE(@column_old_values + @seperator, '') + @column_name 
            FROM deleted

        IF @command = 'UPDATE' or @command = 'INSERT'
            -- Select the new values to populate @column_new_values separated by ', '
            SELECT @column_new_values = COALESCE(@column_new_values + @seperator, '') + @column_name 
            FROM inserted

        FETCH NEXT FROM CURSOR_FOR_COLUMN_NAMES INTO @column_name
    END

    CLOSE CURSOR_FOR_COLUMN_NAMES
    DEALLOCATE CURSOR_FOR_COLUMN_NAMES

    -- Insert into the ChangeLog table
    IF @command = 'UPDATE'
        INSERT INTO ChangeLog (COMMAND, CHANGED_DATE, TABLE_NAME, COLUMN_NAMES, COLUMN_OLD_VALUES, COLUMN_NEW_VALUES, USERNAME)
        VALUES (@command, GETDATE(), 'MyTableName', @column_names, @column_old_values, @column_new_values, USER_NAME())

    IF @command = 'INSERT'
        INSERT INTO ChangeLog (COMMAND, CHANGED_DATE, TABLE_NAME, COLUMN_NAMES, COLUMN_NEW_VALUES, USERNAME)
        VALUES (@command, GETDATE(), 'MyTableName', @column_names, @column_new_values, USER_NAME())

    IF @command = 'DELETE'
        INSERT INTO ChangeLog (COMMAND, CHANGED_DATE, TABLE_NAME, COLUMN_NAMES, COLUMN_OLD_VALUES, USERNAME)
        VALUES (@command, GETDATE(), 'MyTableName', @column_names, @column_old_values, USER_NAME())
END
GO

The current code creates a row with the same values for COLUMN_NAMES, COLUMN_OLD_VALUES, and COLUMN_NEW_VALUES because the code that populates the @column_old_values and @column_new_values actually executes SELECT 'column_name' FROM AnyTable instead of SELECT columnn_name FROM MyTableName but I can’t seem to figure out on how to solve this.

I’ve tried using the EXEC() command, but that seems to partially end my trigger? Even though the code compiles, I continue to receive an error about unclosed quotation marks.

Msg 102, Level 15, State 1, Line 16
Incorrect syntax near ‘=’.

Msg 105, Level 15, State 1, Line 16
Unclosed quotation mark after the character string ‘) + ColumnName1 FROM deleted’.

I think this generic trigger works as soon as this issue is solved but of course, I’d be happy with alternative solutions as well.

Answer

Some errors with your current code include:

• An error when 0 rows are affected, because all the tables would be empty but you did not handle a NULL command, and it generates an error when attempting to insert a NULL command into ChangeLog

• Your cursor would string all affected rows into a strange fashion; even if you got it to work, if 4 rows were affected, you would have 1 row in your ChangeLog where the column_old_values would hold something like (col1, col1, col1, col1, col2, col2, col2, col2).

• Your cursor would need dynamic SQL to use dynamic column names, but dynamic SQL is in a different scope compared to your code, so you need to make a copy of the inserted and deleted trigger-scope tables to use dynamic SQL.

• Your dynamic SQL is trying to use variables that don’t exist in the different scope. It’s a lot easier to debug dynamic SQL if you put it into a string, then print the string for review before attempting to EXEC it.

EDIT:

How about this option, which doesn’t rely on knowing the columns but does rely on knowing the table PK’s beforehand? Those shouldn’t change nearly as often as other columns, and the performance for this is vastly superior to what you were trying to do. This is a sample from one I’ve implemented on a table we weren’t sure was still being utilized by one of our few dozen users, and I needed to track it over a year.

-- Create Trigger for Table to log changes
ALTER TRIGGER AUDIT_MyTableName
ON bookings
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;

    -- Grab trx type
    DECLARE @command char(6) 
    SET @command =
    CASE
        WHEN EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted) THEN 'UPDATE'
        WHEN EXISTS(SELECT 1 FROM inserted) THEN 'INSERT'
        WHEN EXISTS(SELECT 1 FROM deleted) THEN 'DELETE'
        ELSE '0 ROWS' -- if no rows affected, trigger does NOT record an entry
    END 
 
    IF @command = 'INSERT'

        -- Add audit entry
        INSERT INTO ChangeLog (COMMAND, CHANGED_DATE, TABLE_NAME, /*COLUMN_NAMES,*/ COLUMN_OLD_VALUES, COLUMN_NEW_VALUES, USERNAME)
        SELECT 
            Command     = @command, 
            ChangeDate  = GETDATE(), 
            TableName   = 'bookings', 
            --ColNames  = @column_names, 
            Column_OLD_Values   = NULL, 
            Column_NEW_Values   = (SELECT inserted.* for xml path('')), 
            Username    = SUSER_SNAME()
        FROM inserted 
    
    ELSE IF @command = 'DELETE'

        -- Add audit entry
        INSERT INTO ChangeLog (COMMAND, CHANGED_DATE, TABLE_NAME, /*COLUMN_NAMES,*/ COLUMN_OLD_VALUES, COLUMN_NEW_VALUES, USERNAME)
        SELECT 
            Command     = @command, 
            ChangeDate  = GETDATE(), 
            TableName   = 'bookings', 
            --ColNames  = @column_names, 
            Column_OLD_Values   = (SELECT deleted.* for xml path('')), 
            Column_NEW_Values   = NULL,
            Username    = SUSER_SNAME()
        FROM deleted

    ELSE -- is UPDATE 

        -- Add audit entry
        INSERT INTO ChangeLog (COMMAND, CHANGED_DATE, TABLE_NAME, /*COLUMN_NAMES,*/ COLUMN_OLD_VALUES, COLUMN_NEW_VALUES, USERNAME)
        SELECT 
            Command     = @command, 
            ChangeDate  = GETDATE(), 
            TableName   = 'bookings', 
            --ColNames  = @column_names, 
            Column_OLD_Values   = (SELECT deleted.* for xml path('')), 
            Column_NEW_Values   = (SELECT inserted.* for xml path('')), 
            Username    = SUSER_SNAME()
        FROM inserted 
        INNER JOIN deleted ON inserted.bookingID = deleted.bookingID -- join on w/e the PK is
END

The result is perfectly functional for whatever you need:

If you’re willing to change the column types for COLUMN_OLD_VALUES and COLUMN_NEW_VALUES to XML, you can simply add , type right after each for xml path('') and the XML is click-able and easy to read in SSMS.

Column_OLD_Values   = (SELECT deleted.* for xml path(''), type), 
Column_NEW_Values   = (SELECT inserted.* for xml path(''), type),