How to log changed values in Update trigger

Question

I want to log any field changes in table Item to a log table called Events.

CREATE TABLE [dbo].[Items]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](100) NULL,
    [Description] [nvarchar](max) NULL,
    [ParentId] [int] NULL,
    [EntityStatusId] [int] NOT NULL,
    [ItemTypeId] [int] NOT NULL,
    [StartDate] [datetimeoffset](7) NULL,
    [DueDate] [datetimeoffset](7) NULL,
    [Budget] [decimal](18, 2) NULL,
    [Cost] [decimal](18, 2) NULL,
    [Progress] [int] NULL,
    [StatusTypeId] [int] NULL,
    [ImportanceTypeId] [int] NULL,
    [PriorityTypeId] [int] NULL,
    [CreatedDate] [datetimeoffset](7) NULL,
    [HideChildren] [bit] NOT NULL,
    [TenantId] [int] NOT NULL,
    [OwnedBy] [int] NOT NULL,
    [Details] [nvarchar](max) NULL,
    [Inserted] [datetimeoffset](0) NOT NULL,
    [Updated] [datetimeoffset](0) NOT NULL,
    [InsertedBy] [int] NULL,
    [UpdatedBy] [int] NULL,

)

For each changed column, I want to add a row to this table. This table will hold changes for the Item table, but later it will hold changes for other tables as well. I would like the trigger to be as dynamic as possible, so the same basic trigger can be used for other tables as well. If columns are added/removed to a table, the SP should discover that and not break.

CREATE TABLE [dbo].[Events]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [RecordId] [int] NOT NULL, -- Item.Id
    [EventTypeId] [int] NOT NULL, -- Always 2
    [EventDate] [datetimeoffset](0) NOT NULL, --GetUTCDate()
    [ColumnName] [nvarchar](50) NULL, --The column name that changed
    [OriginalValue] [nvarchar](max) NULL, --The original Value
    [NewValue] [nvarchar](max) NULL, --The New Value
    [TenantId] [int] NOT NULL, --Item.TentantId
    [AppUserId] [int] NOT NULL, --Item.ModifiedBy
    [TableName] [int] NOT NULL --The Name of the Table (Item in this case, but later there will be others)

)

I am trying to write an Update trigger, but am finding it difficult.

I know there is are Inserted and Deleted tables that hold the new and old values.

So how do I actually achieve that? It seems that it ought to be dynamic so that if columns are added, it doesn't break anything.

If I were writing this in C#, I would get all the column names and loop through them and find the changed fields, then create an Event for each of them. But I am don't see how to do this with SQL.

UPDATE TO RESPOND TO ANSWER:
This answer works when editing in SSMS. However, in practice, the app uses EntityFramework and it appears to be doing something strange, as this is what gets logged. Note that only one column actually had different values in Original/New. Thus I was trying to check that the values were actually different before doing the insert.

+----+----------+-------------+----------------------------+------------------+----------------------------+----------------------------+----------+-----------+---------+-----------+
| Id | RecordId | EventTypeId |         EventDate          |    ColumnName    |       OriginalValue        |          NewValue          | TenantId | AppUserId | TableId | TableName |
+----+----------+-------------+----------------------------+------------------+----------------------------+----------------------------+----------+-----------+---------+-----------+
| 21 |      397 |           2 | 2018-04-22 15:42:16 +00:00 | Name             | Task 2                     | Task 2A                    |        8 |        11 | NULL    | Item      |
| 22 |      397 |           2 | 2018-04-22 15:42:16 +00:00 | Description      | NULL                       | NULL                       |        8 |        11 | NULL    | Item      |
| 23 |      397 |           2 | 2018-04-22 15:42:16 +00:00 | ParentId         | 238                        | 238                        |        8 |        11 | NULL    | Item      |
| 24 |      397 |           2 | 2018-04-22 15:42:16 +00:00 | EntityStatusId   | 1                          | 1                          |        8 |        11 | NULL    | Item      |
| 25 |      397 |           2 | 2018-04-22 15:42:16 +00:00 | ItemTypeId       | 3                          | 3                          |        8 |        11 | NULL    | Item      |
| 26 |      397 |           2 | 2018-04-22 15:42:16 +00:00 | StartDate        | NULL                       | NULL                       |        8 |        11 | NULL    | Item      |
| 27 |      397 |           2 | 2018-04-22 15:42:16 +00:00 | DueDate          | NULL                       | NULL                       |        8 |        11 | NULL    | Item      |
| 28 |      397 |           2 | 2018-04-22 15:42:16 +00:00 | Budget           | NULL                       | NULL                       |        8 |        11 | NULL    | Item      |
| 29 |      397 |           2 | 2018-04-22 15:42:16 +00:00 | Cost             | NULL                       | NULL                       |        8 |        11 | NULL    | Item      |
| 30 |      397 |           2 | 2018-04-22 15:42:16 +00:00 | Progress         | NULL                       | NULL                       |        8 |        11 | NULL    | Item      |
| 31 |      397 |           2 | 2018-04-22 15:42:16 +00:00 | StatusTypeId     | 1                          | 1                          |        8 |        11 | NULL    | Item      |
| 32 |      397 |           2 | 2018-04-22 15:42:16 +00:00 | ImportanceTypeId | NULL                       | NULL                       |        8 |        11 | NULL    | Item      |
| 33 |      397 |           2 | 2018-04-22 15:42:16 +00:00 | PriorityTypeId   | NULL                       | NULL                       |        8 |        11 | NULL    | Item      |
| 34 |      397 |           2 | 2018-04-22 15:42:16 +00:00 | OwnedBy          | 11                         | 11                         |        8 |        11 | NULL    | Item      |
| 35 |      397 |           2 | 2018-04-22 15:42:16 +00:00 | Details          | <p><span></span></p>       | <p><span></span></p>       |        8 |        11 | NULL    | Item      |
| 36 |      397 |           2 | 2018-04-22 15:42:16 +00:00 | Inserted         | 0001-01-01 00:00:00 +00:00 | 0001-01-01 00:00:00 +00:00 |        8 |        11 | NULL    | Item      |
| 37 |      397 |           2 | 2018-04-22 15:42:16 +00:00 | Updated          | 0001-01-01 00:00:00 +00:00 | 0001-01-01 00:00:00 +00:00 |        8 |        11 | NULL    | Item      |
| 38 |      397 |           2 | 2018-04-22 15:42:16 +00:00 | InsertedBy       | 11                         | 11                         |        8 |        11 | NULL    | Item      |
| 39 |      397 |           2 | 2018-04-22 15:42:16 +00:00 | UpdatedBy        | 11                         | 11                         |        8 |        11 | NULL    | Item      |
+----+----------+-------------+----------------------------+------------------+----------------------------+----------------------------+----------+-----------+---------+-----------+

Possible duplicate of https://stackoverflow.com/questions/1254787/sql-server-update-trigger-get-only-modified-fields — Jake Reece, Apr 21 '18 at 02:11
You should add some explanations to your `Events` table. What are `RecordId, EventTypeId` columns? They are not present in `Items` table. Also what's the purpose of `TableId` column if you are logging only from one table. And what if several columns are updated at the same time, will you insert several rows? — uzi, Apr 21 '18 at 06:07
So you want to, for every updated row AND every updated column to have a new row in your `dbo.Events` table? I'm basing this assumption on having `ColumnName` attribute in it. If this is the right assumption, what you're looking for is writing code that works even when you change `dbo.Items` schema? Just trying to figure out what exactly is it you're looking for, might even add the answers to my questions in your question above so it clarifies your intentions right away when someone reads it. — MK_, Apr 21 '18 at 12:25
@MK_ Good points, I have edited my question above to clarify. — Greg Gum, Apr 21 '18 at 14:18
I figured as such and would've suggested exactly along the lines of what _sticky bit_ suggested in his answer. I don't think there's a more performant or more efficient way to do it. — MK_, Apr 21 '18 at 18:17

score 1 · Answer 1 · answered Apr 21 '18 at 14:32

You could query the catalog (sys.columns, sys.tables, sys.schemas, etc.) to get the columns of the current table into a cursor. Then iterate over that cursor and build your single inserts to the log table as string. Then execute them with EXECUTE or sp_executesql or similar. (Note, that the linked documentation does not necessarily match the version of your DBMS and is just meant as a first hint.)

By the way, you might want to change the datatype of [TableName] and [ColumnName] to sysname which is also used in the catalog for such columns.

score 1 · Accepted Answer · edited Apr 24 '18 at 01:08

1

Here's one way using COLUMNS_UPDATED. Trigger does not depend on column names, so you can add or remove columns without problem. I have added some comments in the query

create trigger audit on Items
after update
as
begin
    set nocount on;
        create table #updatedCols (Id int identity(1, 1), updateCol nvarchar(200))

        --find all columns that were updated and write them to temp table
        insert into #updatedCols (updateCol)
        select
            column_name
        from
            information_schema.columns
        where   
            table_name = 'Items'   
            and convert(varbinary, reverse(columns_updated())) & power(convert(bigint, 2), ordinal_position - 1) > 0

        --temp tables are used because inserted and deleted tables are not available in dynamic SQL
        select * into #tempInserted from inserted
        select * into #tempDeleted from deleted

        declare @cnt int = 1
        declare @rowCnt int
        declare @columnName varchar(1000)
        declare @sql nvarchar(4000)

        select @rowCnt = count(*) from #updatedCols

        --execute insert statement for each updated column
        while @cnt <= @rowCnt
        begin
            select @columnName = updateCol from #updatedCols where id = @cnt

            set @sql = N'
                insert into [Events] ([RecordId], [EventTypeId], [EventDate], [ColumnName], [OriginalValue], [NewValue], [TenantId], [AppUserId], [TableName])
                select
                    i.Id, 2, GetUTCDate(), ''' + @columnName + ''', d.' + @columnName + ', i.' + @columnName +', i.TenantId, i.UpdatedBy, ''Item''
                from
                    #tempInserted i
                    join #tempDeleted d on i.Id = d.Id and isnull(Cast(i.' + @columnName + ' as varchar), '''') <> isnull(Cast(d.' +@columnName + ' as varchar), '''')
                '
            exec sp_executesql @sql
            set @cnt = @cnt + 1
        end
end

I have changed data type of TableName column of Events table to nvarchar.

edited Apr 24 '18 at 01:08

Greg Gum

33,478
39
162
233

answered Apr 21 '18 at 18:53

uzi

4,118
1
15
22

One issue I found is that it logs everything, not just changed fields. – Greg Gum Apr 22 '18 at 03:01
This is the best I could come up with, but it never logs anything `set @sql = N' Select case when Exists( select 1 from #tempInserted i join #tempDeleted d on i.Id = d.Id where i.' + @columnName + '!= d.' + @columnName + ') then cast(1 as bit) else cast(0 as bit) end ' exec @isChanged = sp_executesql @sql if @isChanged = 1` – Greg Gum Apr 22 '18 at 03:03
I have checked the trigger. Everything works as expected, only changed columns are logged. Can you show your update statement? And what columns are logged? – uzi Apr 22 '18 at 05:27
I retested and your are correct. It works perfectly. Thanks very much. – Greg Gum Apr 22 '18 at 15:32
@uze, I tested once and it worked correctly. And then tested again, and it shows all columns were updated. I updated the question above to show the results after the "Name" field was updated. – Greg Gum Apr 22 '18 at 15:44
I did not understand what have you updated? I don't see any changes. Are you updating only `Name` column? I have updated that column several times, and my results are correct. – uzi Apr 22 '18 at 15:56
I guess that's because your update statement in application includes all those columns. All those columns will be considered as changed, even if you are passing old values. Try to change join to `join #tempDeleted d on i.Id = d.Id and isnull(i.' + @columnName + ', '''') <> isnull(d.' +@columnName + ', '''')` – uzi Apr 22 '18 at 17:15
When I used the join with the nullcheck as above, it works in SSMS. But in EntityFramework, it gives the error 'Could not convert nvarchar to int' However, when I took out the nullcheck and only checked that the values did not match, then it worked. That really makes no sense to me. – Greg Gum Apr 22 '18 at 21:20
`join #tempDeleted d on i.Id = d.Id and i.' + @columnName + ' <> d.' +@columnName + '` – Greg Gum Apr 22 '18 at 21:22
Are you sure you are passing correct values? According to error message that is not because of null check. Recheck the values that you pass to update statement. Without null check trigger will not log columns that were update from or to null. Also try `coalesce` instead of `isnull` – uzi Apr 23 '18 at 04:32
@uze, I updated your answer. I finally got this to work with a cast. The problem was with decimal columns. – Greg Gum Apr 24 '18 at 01:09
1

Glad it worked. But don't forget to set length of `varchar` during casting. – uzi Apr 24 '18 at 02:49
This does not work correctly. Also Microsoft advise against the use of ORDINAL POSITION (The ORDINAL_POSITION column of the INFORMATION_SCHEMA.COLUMNS view is not compatible with the bit pattern of columns returned by COLUMNS_UPDATED). https://learn.microsoft.com/en-us/sql/t-sql/functions/columns-updated-transact-sql?view=sql-server-ver16 – VasilisP Jan 25 '23 at 11:58

How to log changed values in Update trigger

2 Answers2