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 |
+----+----------+-------------+----------------------------+------------------+----------------------------+----------------------------+----------+-----------+---------+-----------+
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.