Recursive AI

Log SQLite updates with a trigger in C#

OneLite is a free, open-source SQLite-compatible database for .NET from Recursive AI. MIT or Apache-2.0, no native dependencies.

A trigger runs SQL automatically when a row changes. Use it to keep a history of status updates without adding a separate log call to each C# update.

Terminal
dotnet add package RecursiveAI.OneLite

Requires .NET 10 (net10.0); .NET 9 and earlier aren’t supported.

C#
using RecursiveAI.OneLite;
 
await using var db =
    await Database.OpenAsync(":memory:");
await db.ExecuteScriptAsync("""
    CREATE TABLE items(id, status);
    CREATE TABLE history(item_id, status);
    CREATE TRIGGER log_update
    AFTER UPDATE ON items BEGIN
      INSERT INTO history
      VALUES(NEW.id, NEW.status);
    END;
    INSERT INTO items VALUES(1, 'new');
    UPDATE items SET status = 'sent'
    WHERE id = 1;
    """);
var status = await db.ScalarAsync<string>(
    $"SELECT status FROM history");
Console.WriteLine(status);

OneLite binds interpolated values like {value} as SQL parameters rather than joining them into the SQL text; ExecuteAsync and ScalarAsync require the $ prefix even when there are no values to bind.

Prints the status read back from the history table.

Text
sent

Unzip the example, then dotnet run.

The full program

C#
using RecursiveAI.OneLite;
 
await using var db = await Database.OpenAsync(":memory:");
await db.ExecuteScriptAsync("""
    CREATE TABLE items(id INTEGER PRIMARY KEY, status TEXT);
    CREATE TABLE history(
      seq INTEGER PRIMARY KEY, item_id INTEGER,
      old_status TEXT, new_status TEXT
    );
    INSERT INTO items VALUES(1, 'new'), (2, 'new');
    CREATE TRIGGER log_update
    AFTER UPDATE OF status ON items
    WHEN OLD.status IS NOT NEW.status
    BEGIN
      INSERT INTO history(item_id, old_status, new_status)
      VALUES(NEW.id, OLD.status, NEW.status);
    END;
    """);
 
var status = "sent";
await db.ExecuteAsync($"UPDATE items SET status = {status}");
await db.ExecuteAsync($"UPDATE items SET status = {status}");
 
var changes = await db.QueryAsync<string>($"""
    SELECT item_id || ': ' || quote(old_status) || ' -> ' || quote(new_status)
    FROM history ORDER BY seq
    """);
foreach (var change in changes)
    Console.WriteLine(change);
Text
1: 'new' -> 'sent'
2: 'new' -> 'sent'

The full program records both values for each item. The first update changes two rows; the second sets them to the same status again. UPDATE OF status selects statements that assign status; WHEN OLD.status IS NOT NEW.status skips rows where the value is unchanged.

If the history table is missing when you run the update, OneLite throws OneLiteException; the update does not go through. This history is part of the same database, not a separate or tamper-resistant audit trail.

Variations

Record the initial status too. Add this trigger to the full program’s setup script before INSERT INTO items. It records NULL as the previous status for the initial insert.

SQL
CREATE TRIGGER log_insert
AFTER INSERT ON items BEGIN
  INSERT INTO history(item_id, old_status, new_status)
  VALUES(NEW.id, NULL, NEW.status);
END;

The output now starts with these two rows:

Text
1: NULL -> 'new'
2: NULL -> 'new'

The short answer

Create a history table and an AFTER UPDATE trigger on the table you want to track. Inside the trigger, insert the row’s key and NEW values into the history table. In OneLite, ExecuteScriptAsync installs the trigger and can run the rest of the sample setup. The C# update is an ordinary UPDATE: there’s no extra logging call for the application to remember.

Use a stable key such as items.id in the history table so you can find changes for one item later. Give the history its own key (seq in the full program) so that repeated updates to the same item can each have a row.

What are OLD and NEW in SQLite triggers?

OLD.column reads the value before the update; NEW.column reads the value after it. In the full program, OLD.status is new and NEW.status is sent for both items. According to the SQLite trigger reference, an UPDATE trigger can use both names, an INSERT trigger can use only NEW, and a DELETE trigger can use only OLD. An update trigger won’t capture the initial insert or a later delete. Add separate triggers if those events matter.

UPDATE OF status checks whether the statement names status in its SET list, not whether the stored value changed. The WHEN clause handles that second test. In SQLite, IS NOT treats a change from NULL to a value or back as a difference, and two NULLs as equal. A plain <> comparison doesn’t give you that behavior for nullable columns.

How do you set a SQLite update trigger for each row?

SQLite triggers run once per affected row; FOR EACH ROW is optional. That’s why one UPDATE of two items can write two history entries. Use AFTER for recording changes rather than altering the original row inside a BEFORE trigger. The trigger reference describes why SQLite recommends AFTER triggers where possible.

The history write belongs to the same transaction as the update. If you roll back that transaction, you roll back the history entries too. That makes the table useful for tracking committed state changes, not failed attempts. To record when a change happened, add changed_at TEXT DEFAULT CURRENT_TIMESTAMP to the history table; to record who made it, have your app supply the user ID.

All onelite recipes