T-SQL Trigger – Audit Column Change

Given a simple table, with an ID what is the correct way to audit a column being changed. I am asking after looking after various answers which seem not to be working.

Here is what I have:

Create Table Tbl_Audit
(
    AuditId int identity(1,1) not null,
    Tbl_Id int not null.
    Tbl_Old_ColumnValue varchar(255),
    Tbl_New_ColumnValue varchar(255)
)

GO

Create Trigger Tr_Tbl_ColumnChanged on Tbl
after insert, update
As
begin
    if(update(ColumnName))
        begin
            insert into tbl_audit
            (
                Tbl_Id,
                Tbl_Old_ColumnName,
                Tbl_New_ColumnName
            )
            select
                tbl.PKId,
                tbl.ColumnName,
                i.ColumnName,
            from
                Tbl tbl join 
                inserted i
                on tbl.PKId = i.PKId
            
end

What I see is thousands of examples where Tbl_Old_ColumnValue = Tbl_New_ColumnValue , which is not what I want.

I would expect to run:

select top 10 * from tbl_audit where Tbl_Old_ColumnValue !=Tbl_New_ColumnValue 

But this returns no results.

In order to get results of columns that actually changed, I need to run a very expensive query:

select top 10 
old.AuditId,
old.Tbl_Old_ColumnValue,
new.Tbl_Old_ColumnValue  as [Tbl_New_ColumnValue] 
from tbl_audit [old]
join Tbl_Audit [new]
on [ol].Tbl_Id= [new].Tbl_Id and [old].AuditId != [new].AuditId
where [old].Tbl_Old_ColumnValue != [new].Tbl_Old_ColumnValue 

Results:

AuditId Tbl_Id  Tbl_Old_ColumnValue Tbl_New_ColumnValue
10051       1       old_value           old_value
10052       1       new_value           new_value

But that doesn’t produce what I expect:

AuditId Tbl_Id  Tbl_Old_ColumnValue Tbl_New_ColumnValue
10057       1   old_value           Some New Value

Oddly, If I modify the column directly via SSMS using:

update Tbl set Tbl.ColumnValue = 'Some New Value'

I see what I expect from my trigger:

AuditId Tbl_Id  Tbl_Old_ColumnValue Tbl_New_ColumnValue
10057       1   old_value           Some New Value

What am I doing wrong?

Also, how do I eliminate auditing of row where update(ColumnName) is actually false. IE, the ColumnName (even if being set) is not audit when it is being set to the previous/old value.

Answer

update(ColumnName) doesn’t mean that the value has changed, just that that column was involved in the insert/update – and it will always be involved in an insert. You need to compare the old and new values using inserted and deleted e.g.

insert into tbl_audit
(
    Tbl_Id,
    Tbl_Old_ColumnName,
    Tbl_New_ColumnName
)
select
    tbl.PKId,
    tbl.ColumnName,
    i.ColumnName,
from
    inserted i
    left join deleted d on d.PKId = i.PKId
    -- Insert d.PKId is null, there are no records in deleted
    where d.PKId is null
    -- Change from null to value
    or (i.ColumnName is null and d.ColumnName is not null)
    -- Change from value to null
    or (i.ColumnName is not null and d.ColumnName is null)
    -- Change in value
    or i.ColumnName <> d.ColumnName;

You can potentially simplify the null check using coalesce and a suitable value which will never actually occur in your data.

The documentation is actually pretty good on all this.

And if the column is not always included in an update, then the update(ColumnName) test is still worth doing because it speeds up the trigger, and triggers should be as fast as possible. Personally I short circuit out early e.g. if not update(ColumnName) return;

Obviously you need to adapt that logic to handle all the columns you are auditing.