---
title: "Log SQLite updates with a trigger in C#"
url: https://recursiveai.org/onelite/cookbook/log-sqlite-updates-with-trigger/
description: "Use a SQLite AFTER UPDATE trigger in C# with OneLite. Write row changes to a history table and record the old and new values only when they differ."
version: "1.0.0"
---

# 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.

[NuGet](https://www.nuget.org/packages/RecursiveAI.OneLite) · [GitHub](https://github.com/recursive-ai-oss/onelite-csharp) · [Docs](https://recursiveai.org/onelite/docs/) · [Download example (.zip)](https://recursiveai.org/onelite/cookbook/log-sqlite-updates-with-trigger/log-sqlite-updates-with-trigger.zip)

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.

```shell
dotnet add package RecursiveAI.OneLite
```

Requires [.NET 10](https://dotnet.microsoft.com/download/dotnet/10.0) (net10.0); .NET 9 and earlier aren’t supported.

```csharp
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](https://recursiveai.org/onelite/cookbook/log-sqlite-updates-with-trigger/log-sqlite-updates-with-trigger.zip), then `dotnet run`.

## The full program

```csharp
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'
```

## Related recipes

- [Run SQLite transactions in C#](https://recursiveai.org/onelite/cookbook/sqlite-transactions-csharp/)
- [Update SQLite rows from another table in C#](https://recursiveai.org/onelite/cookbook/update-sqlite-rows-from-another-table/)

## 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](https://www.sqlite.org/lang_createtrigger.html), 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`](https://www.sqlite.org/lang_expr.html#isdf) treats a change from `NULL` to a value or back as a difference, and two `NULL`s 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](https://www.sqlite.org/lang_createtrigger.html#cautions_on_the_use_of_before_triggers) 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.
