Change Logs as JSON: Old and New Values in One Column

Change logs as JSON let you keep old and new values in a single column. You do not need a history column for every business field. But you still decide which fields are tracked, and you must test what happens with NULL.

The question that starts every audit table

Somebody asks, “Who changed this label, and what was it before?” You have no idea. So you add an audit table with an Old_Label column, a New_Label column, and the same pair for every other field. Six months later the audit table is wider than the real one.

There is a lighter way. Store one row per changed record, and put the changed fields inside a JSON payload. The row stays small, and the payload only mentions what actually changed.

Set up two tables

JsonItems is the business table. JsonChanges is the log. A CHECK constraint with ISJSON makes sure only valid JSON gets into the payload. Item 1 has the label A. Item 2 has a NULL label, because NULL is where audit logic usually goes wrong. The demo drops both tables at the end.

DROP TABLE IF EXISTS dbo.JsonItems;
DROP TABLE IF EXISTS dbo.JsonChanges;

CREATE TABLE dbo.JsonItems (
    Id     int PRIMARY KEY,
    Label  nvarchar(30)  NULL,
    Amount decimal(12,2) NULL);

CREATE TABLE dbo.JsonChanges (
    ItemId       int,
    ChangedAtUtc datetime2,
    Payload      nvarchar(max) CHECK (ISJSON(Payload) = 1));

INSERT dbo.JsonItems VALUES (1, N'A', 10), (2, NULL, 20);

Why NULL needs IS DISTINCT FROM

The obvious test for “did it change” is a not-equals sign. With NULL, that test says nothing at all. Compare a NULL label to the word Changed and watch both operators.

SELECT CASE WHEN CAST(NULL AS nvarchar(30)) <> N'Changed'
            THEN 'logged' ELSE 'missed' END AS with_not_equals,
       CASE WHEN CAST(NULL AS nvarchar(30)) IS DISTINCT FROM N'Changed'
            THEN 'logged' ELSE 'missed' END AS with_is_distinct_from;

The not-equals version says “missed.” IS DISTINCT FROM says “logged.” So the trigger uses IS DISTINCT FROM, and an item that goes from NULL to a value is never lost.

Build the trigger

A trigger fires once per statement, not once per row. So it must handle many rows at once. It joins the inserted and deleted sets on Id, which is why Id must never change. The trigger throws an error if anyone tries. For each tracked field it builds a small old-and-new object, or nothing when the value did not change. ABSENT ON NULL drops the empty ones.

CREATE TRIGGER dbo.trJsonItems ON dbo.JsonItems AFTER UPDATE AS
BEGIN
    SET NOCOUNT ON;
    IF UPDATE(Id) THROW 50000, N'Item IDs cannot be changed through this table.', 1;

    INSERT dbo.JsonChanges (ItemId, ChangedAtUtc, Payload)
    SELECT i.Id, SYSUTCDATETIME(),
           JSON_OBJECT(
             'Label':  JSON_QUERY(CASE WHEN i.Label IS DISTINCT FROM d.Label
                          THEN JSON_OBJECT('old': d.Label, 'new': i.Label) END),
             'Amount': JSON_QUERY(CASE WHEN i.Amount IS DISTINCT FROM d.Amount
                          THEN JSON_OBJECT('old': d.Amount, 'new': i.Amount) END)
             ABSENT ON NULL)
    FROM inserted AS i
    JOIN deleted AS d ON d.Id = i.Id
    WHERE i.Label IS DISTINCT FROM d.Label OR i.Amount IS DISTINCT FROM d.Amount;
END;

Now update both rows in one statement, then update Amount to its own value.

UPDATE dbo.JsonItems SET Label = N'Changed';
SELECT ItemId, Payload FROM dbo.JsonChanges ORDER BY ItemId;

UPDATE dbo.JsonItems SET Amount = Amount;
SELECT COUNT(*) AS LoggedChanges FROM dbo.JsonChanges;

One statement wrote two payloads. Item 1 went from A to Changed. Item 2 went from null to Changed, and the old null is kept. Assigning Amount to itself changed nothing, so the log still holds 2 rows.

Read the payload without losing NULL

OPENJSON turns the payload back into rows, so a generic audit screen can show them. The last two statements try to change an Id, and then show that the table is untouched.

SELECT c.ItemId, j.[key] AS ChangedColumn, v.OldValue, v.NewValue
FROM dbo.JsonChanges AS c
CROSS APPLY OPENJSON(c.Payload) AS j
CROSS APPLY OPENJSON(j.value)
    WITH (OldValue nvarchar(4000) '$.old', NewValue nvarchar(4000) '$.new') AS v
ORDER BY c.ItemId, j.[key];

BEGIN TRY
    UPDATE dbo.JsonItems SET Id = 9 WHERE Id = 2;
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS KeyChangeError;
END CATCH;

SELECT Id, Label FROM dbo.JsonItems ORDER BY Id;

The key change fails with error 50000, and the ids are still 1 and 2. The old label of item 2 reads as a real SQL NULL, not the text “null”.

Keep the contract maintained

JSON saves you from adding audit columns. It does not make the trigger maintain itself. When you add a tracked column, you must edit the trigger and test it. Leave secrets such as passwords out of the log. These log rows share the business transaction, so a rolled-back update leaves no history. A log of failed attempts is a different tool.

DROP TABLE IF EXISTS dbo.JsonItems;
DROP TABLE IF EXISTS dbo.JsonChanges;

Keep the tracked-field list and its tests in the same place.

An audit payload is not a reporting schema, it is a record of what changed.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.