Audit trigger to only track when specific columns change, and only log the old and new values for the changed columns

Question

I have a table with 75 columns and need to track about 15 of those columns to log out the old and new values anytime the data in any of those 15 columns change (updates only, no inserts or deletes). The exception is that we only want to track the columns that are updated out of those 15. If the old value and new values are both NULL or both the same, we only want to log NULLs to the audit table for those columns.

Multiple columns are nullable, and are a combination of integer, decimal and nvarchar data types.

This is all running on an Azure SQL DB.

I've written an initial after update trigger that has an insert statement into an audit table with 30 columns, one "old" and "new" column for all 15 we wish to track. This all works great when the old and new values are both not NULL already, but we get missing data if one of the old or new values does happen to be NULL.

In order to account for this, I've started down the road of writing case statements for each column, and a bunch of where clauses, but it doesn't feel like this is the right road to go down. Basically, 15 different variations of statements like:

CREATE OR ALTER TRIGGER [dbo].[trg_Stuff_Audit]
ON [dbo].[Stuff]
AFTER UPDATE
AS
BEGIN

insert into dbo.audit (id, OldDecimalValue, NewDecimalValue)

select i.id,

case when d.DecimalValue is null and i.DecimalValue is not null then d.DecimalValue
when d.DecimalValue is not null and i.DecimalValue is null then d.DecimalValue
when d.DecimalValue <> i.DecimalValue then d.DecimalValue
else NULL end as OldDecimalValue,

case when d.DecimalValue is null and i.DecimalValue is not null then i.DecimalValue
when d.DecimalValue is not null and i.DecimalValue is null then i.DecimalValue
when d.DecimalValue <> i.DecimalValue then i.DecimalValue
else NULL end as NewDecimalValue

from inserted i inner join deleted d on i.id = d.id

where d.DecimalValue is null and i.DecimalValue is not null
OR d.DecimalValue is not null and i.DecimalValue is null
OR d.DecimalValue <> i.DecimalValue

END;

I feel there has to be a better way to solve for this, am I going down the right road or do I need to change course?

Answer 1

I think your best approach would be to just modify your existing trigger to properly handle NULL for either old or new values. Since you are in the cloud, you can use the IS DISTINCT FROM that CharlieFace references in your WHERE clause.

WHERE I.DecimalValue IS DISTINCT FROM D.DecimalValue
    OR I.DecimalValue2 IS DISTINCT FROM D.DecimalValue2
    /** REPEAT **/

That will probably be your fastest path to a good answer.

Personally, I lean away from temporal tables because they do track EVERYTHING and like you I have very specific requirements and staying with the specific requirements makes it faster.

Which leads me to also shy away from more complicated solutions like Charlieface recommends. When it's one or two rows at a time, then it doesn't matter that much, but when you have potentially hundreds+ rows at a time for the trigger to scan through then the fact that the INSERTED and DELETED tables being heaps means that throughput will suffer greatly. A quick and dirty insert into an audit table that's probably read rarely is far better, even if re-constructing the events in a clean manner is a little more work.

If you want to stay with showing NULL when both Old and New are the same, then you could do this..

SELECT I.ID 
    , DecimalValue_NEW = ISNULL(NULLIF(I.DecimalValue, D.DecimalValue), NULLIF(D.DecimalValue, I.DecimalValue))
    , DecimalValue_OLD = ISNULL(NULLIF(D.DecimalValue, I.DecimalValue), NULLIF(I.DecimalValue, D.DecimalValue))
    , DecimalValue2_NEW = ISNULL(NULLIF(I.DecimalValue2, D.DecimalValue2), NULLIF(D.DecimalValue2, I.DecimalValue2))
    , DecimalValue2_OLD = ISNULL(NULLIF(D.DecimalValue2, I.DecimalValue2), NULLIF(I.DecimalValue2, D.DecimalValue2))
FROM INSERTED AS I
    INNER JOIN DELETED AS D ON D.ID = I.ID 
WHERE I.DecimalValue IS DISTINCT FROM D.DecimalValue
    OR I.DecimalValue2 IS DISTINCT FROM D.DecimalValue2

See https://stackoverflow.com/questions/1075142/how-to-compare-values-which-may-both-be-null-in-t-sql for where I got the ISNULL(NULLIF(A,B), NULLIF(B,A)) construct...

My rule for triggers is to do the work fast, don't attempt to analyze the data if you can help it and get out.... and make the operation bulletproof so that it only throws an exception if you need it to throw an exception.

Answer 2

You could maybe make dbo.stuff a temporal/versioned table. Each row will have a start and end time with the old rows being put into the history table.

Temporal Tables

Answer 3

I recommend going down the route of using Temporal Tables like the other answer.

---

But if you still want to stick to triggers, you can simplify this quite a bit, by unpivoting the values and comparing in one go.

In other words: don't store the whole row again, just store a pair of OldValue and NewValue columns, each row representing a single change to a column per row.

CREATE OR ALTER TRIGGER [dbo].[trg_Stuff_Audit]
ON [dbo].[Stuff]
AFTER INSERT, UPDATE, DELETE
AS

SET NOCOUNT ON;

IF NOT EXISTS (SELECT 1 FROM inserted) AND NOT EXISTS (SELECT 1 FROM deleted)
    RETURN;

INSERT dbo.audit (id, ColumnName, OldDecimalValue, NewDecimalValue)
SELECT
  ISNULL(i.id, d.id),
  v.ColumnName
  v.OldValue,
  v.NewValue
FROM inserted i
JOIN deleted d on i.id = d.id
CROSS APPLY (
    SELECT *
    FROM (VALUES
      ('decimalValue1', d.decimalValue1, i.decimalValue1),
      ('decimalValue2', d.decimalValue2, i.decimalValue2),
      ('decimalValue3', d.decimalValue3, i.decimalValue3)
    ) v(ColumnName, OldValue, NewValue)
    WHERE v.OldValue IS DISTINCT FROM v.NewValue
) v;

If you have different data types then you need to cast to sql_variant

...
    FROM (VALUES
      ('decimalValue1', CAST(d.decimalValue1 AS sql_variant), CAST(i.decimalValue1 AS sql_variant)),
      ('string2',       d.string2,       i.string2),
      ('int3',          d.int3,          i.int3)
    ) v(ColumnName, OldValue, NewValue)
...

On versions older than SQL Server 2022 you need to change the WHERE

    WHERE v.OldValue <> v.NewValue
       OR v.OldValue IS NULL and v.NewValue IS NOT NULL
       OR v.OldValue IS NOT NULL and v.NewValue IS NULL

Note that I have used AFTER INSERT, UPDATE, DELETE in order to fully track all changes to a table. Also, note that the unpivot works after the join, it does not scan the inserted and deleted tables multiple times