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?