title: How to log changed values in Update trigger
body: <p>I want to log any field changes in table <code>Item</code> to a log table called Events.</p>
  
  <pre><code>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,
  
  )
  </code></pre>
  
  <p>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.</p>
  
  <pre><code>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)
  
  )
  </code></pre>
  
  <p>I am trying to write an Update trigger, but am finding it difficult.</p>
  
  <p>I know there is are Inserted and Deleted tables that hold the new and old values.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>UPDATE TO RESPOND TO ANSWER:<br>
  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.</p>
  
  <pre><code>+----+----------+-------------+----------------------------+------------------+----------------------------+----------------------------+----------+-----------+---------+-----------+
  | 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          | &lt;p&gt;&lt;span&gt;&lt;/span&gt;&lt;/p&gt;       | &lt;p&gt;&lt;span&gt;&lt;/span&gt;&lt;/p&gt;       |        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      |
  +----+----------+-------------+----------------------------+------------------+----------------------------+----------------------------+----------+-----------+---------+-----------+
  </code></pre>