0

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?

3 Answers 3

0

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.

3
  • I did not try IS DISTINCT FROM, that was a very quick update to my trigger and does appear to work as intended. I'll do some more testing on the full dataset, but this looks to be a great option!
    – Lando_
    Commented Apr 20, 2023 at 14:59
  • inserted and deleted are not heaps, they are memory tables. There is no difference between your code and mine in the join, mine just unpivots after joining. Also not sure what you are trying to do with that ISNULL(NULLIF thing but I don't think it handles nulls correctly. Commented Apr 20, 2023 at 15:07
  • Heap, i.e. without indexes - learn.microsoft.com/en-us/sql/relational-databases/triggers/…, although you are right about memory resident, where do you think it spills to when it exceeds available memory? Commented Apr 21, 2023 at 17:52
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

2
  • Do note this will log all columns of the table, and enforces some restrictions on the parent table that one should be aware of.
    – J.D.
    Commented Apr 20, 2023 at 12:14
  • This may be something we consider longer term, as temporal tables do have a place. Right now, we are looking for a more customizable solution so I don't believe it is the best approach for now, but I appreciate the answer.
    – Lando_
    Commented Apr 20, 2023 at 15:01
1

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

4
  • Thank you for showing off that IS DISTINCT FROM, I learned something new! learn.microsoft.com/en-us/sql/t-sql/queries/… Commented Apr 20, 2023 at 14:06
  • Considering it got introduced in the ANSI-SQL:1999 spec, it's about time Commented Apr 20, 2023 at 14:10
  • I was not aware that IS DISTINCT FROM is now supported in Azure SQL, I learned something new as well!. I modified my existing trigger code similarly to how Jonathan suggested and it appears to be working as intended and should satisfy the requirements (until they change again, of course). Definitely may look at the unpivot route longer term.
    – Lando_
    Commented Apr 20, 2023 at 15:06
  • Note that the unpivot works after the join, it does not scan the inserted and deleted tables multiple times Commented Apr 20, 2023 at 15:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.