SQL Server: Capturing All the columns that have changed in a separate table

Question

In my SQl Server I have a table of around 40 attributes/columns. There is a daily load which might update any of these columns. I want to capture the changes in these columns in a separate table with a reason code column telling which column value changed. There might be instances where more than one column value might get changed in a single daily load, in that case the changed log table should capture all these changes separately in rows with each row depicting the individual change.

For Example:

TableA(column1(pk),column2,column3,column4) values(1,100,ABC,999)

After update:

TableA(column1(pk),column2,column3,column4)

values(1,100,ACD,901)

The corresponding change log table should have two entries:

TabChangeLog(column1,before,after,reason);

values(1,ABC,ACD,'column3 changed')

values(1,999,901,'column4 changed')

I tried implementing this through triggers but am not able to figure out a way to separate each of these changes in separate rows when there are more than one changes. Please help

Answer 1

You need to create a trigger like :

create trigger trigger_name
instead of update as

if update(column1)
begin
insert into TabChangeLog
select inserted.column1, inserted.column3, deleted.column3, 'column3', 'update/change'
from inserted i inner join deleted d 
on i.column1 = d.column2
end

if update(column2)
begin
insert into TabChangeLog
select inserted.column1, inserted.column2, deleted.column2, 'column2', 'update/change'
from inserted i inner join deleted d 
on i.column1 = d.column2
end
...

https://www.tutorialgateway.org/instead-of-update-triggers-in-sql-server/

Answer 2

Microsoft SQL Server 2016 has a thing called Temporal Tables which would probably simplify your job a lot. It lets you rewind a dataset through time to see the changes:

https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-2017

If you don't want to go that route and use triggers instead. UPDATE triggers have two tables inserted and deleted that let you know what the row state was before and after.

*Edit: These are tables so you have to use SELECT INTO etc to interact with them you can't do conditional logic (if /else)

CREATE TABLE [dbo].[Table1](
    [Id] [int] NOT NULL,
    [Tail] [int] NOT NULL,
 CONSTRAINT [PK_Table1_1] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)
)


CREATE TABLE Table1_Audit
(
Audit varchar(100)
)

--drop trigger Table1_OnUPDATE

CREATE TRIGGER Table1_OnUPDATE 
   ON  dbo.Table1
   AFTER UPDATE
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for trigger here

    INSERT INTO Table1_Audit ([Audit])
    select CONCAT('Tail changed to' ,inserted.Tail,' for pk Id=',inserted.Id) from inserted inner join
    deleted on inserted.Id = deleted.Id  --pk must be the same
    where
    inserted.Tail <> deleted.Tail --field x must be different

END
GO

--truncate table Table1_Audit

--update Table1 set Tail = 5

select * from Table1_Audit