Yes. The confusing part here is not SQL itself; it is a **SQLite-specific behavior** around `INSERT OR REPLACE` and triggers.

## First: what is `PRAGMA`?

Think of `PRAGMA` as SQLite's **settings/control-panel command**.

Normal SQL manipulates your data:

```sql
SELECT ...
INSERT ...
UPDATE ...
DELETE ...
```

`PRAGMA` changes or inspects **how SQLite itself behaves**.

For example:

```sql
PRAGMA recursive_triggers;
```

asks SQLite:

> "Are recursive triggers enabled on this connection?"

It returns:

```text
0   -- OFF
```

or:

```text
1   -- ON
```

And:

```sql
PRAGMA recursive_triggers = ON;
```

turns that behavior on. SQLite documents `recursive_triggers` as a setting that controls recursive/nested trigger execution for the database connection. ([SQLite][1])

So mentally:

```text
SQL     → work with my data
PRAGMA  → configure/check SQLite itself
```

---

# What's happening in your example

Imagine your table contains:

```text
entries
+-----+----------+
| key | value    |
+-----+----------+
| a   | one      |
+-----+----------+
```

And suppose you created a trigger whose intention is:

> "Rows in `entries` must never be deleted because this table is append-only."

Something conceptually like:

```sql
CREATE TRIGGER prevent_delete
BEFORE DELETE ON entries
BEGIN
    SELECT RAISE(ABORT, 'entries is append-only');
END;
```

So naturally you'd expect this to protect the row.

Then your application executes:

```sql
INSERT OR REPLACE INTO entries
VALUES ('a', 'three');
```

Because `a` already exists, there is a conflict.

And here is the important SQLite detail:

### `REPLACE` isn't really an UPDATE

For a `PRIMARY KEY` or `UNIQUE` conflict, SQLite's `REPLACE` behavior is roughly:

```text
Find conflicting existing row
          ↓
DELETE existing row
          ↓
INSERT new row
```

SQLite's documentation explicitly describes `REPLACE` this way. ([SQLite][2])

So:

```sql
INSERT OR REPLACE INTO entries VALUES ('a', 'three');
```

is conceptually closer to:

```sql
DELETE FROM entries WHERE key = 'a';

INSERT INTO entries VALUES ('a', 'three');
```

than to:

```sql
UPDATE entries
SET value = 'three'
WHERE key = 'a';
```

That distinction is the entire source of your problem.

---

# Now comes the surprising part

You have a `BEFORE DELETE` trigger, so you would reasonably expect:

```text
REPLACE
   ↓
DELETE old row
   ↓
BEFORE DELETE trigger
   ↓
ERROR: entries is append-only
```

But SQLite has a special rule:

> When `REPLACE` deletes an existing row because of a constraint conflict, **DELETE triggers run only when recursive triggers are enabled.** ([SQLite][2])

This is the unintuitive bit.

So your two cases behave differently.

### `recursive_triggers = ON`

```text
Existing row:
a → one

INSERT OR REPLACE
a → three

        ↓

SQLite needs to replace "a"

        ↓

Internally DELETE a → one

        ↓

BEFORE DELETE trigger FIRES

        ↓

"entries is append-only"

        ↓

REPLACE fails
```

Therefore the original row survives:

```text
a → one
```

---

### `recursive_triggers = OFF`

Now:

```text
Existing row:
a → ORIGINAL

INSERT OR REPLACE
a → OVERWRITTEN

        ↓

SQLite needs to replace "a"

        ↓

Internally DELETE a → ORIGINAL

        ↓

DELETE trigger DOES NOT FIRE

        ↓

INSERT a → OVERWRITTEN

        ↓

Success
```

And your table becomes:

```text
a → OVERWRITTEN
```

SQLite confirms this exact special case in its documentation. ([SQLite][2])

---

# Why is it called `recursive_triggers`?

This name makes the situation harder to understand.

Normally, "recursive triggers" means something like:

```text
UPDATE table
   ↓
Trigger A runs
   ↓
Trigger A performs another UPDATE
   ↓
Trigger runs again
   ↓
...
```

So you'd expect this setting only to matter when triggers call other triggers.

But SQLite also uses this setting to decide whether **DELETE triggers caused indirectly by `REPLACE`** should run. SQLite explicitly documents that special behavior. ([SQLite][1])

So in this particular case, don't read:

```text
recursive_triggers
```

as simply:

```text
"Can my trigger call itself?"
```

Think:

```text
"Should SQLite allow certain nested/indirect
trigger executions?"
```

And `REPLACE`'s hidden deletion is one of those cases.

---

# Why this is dangerous for an append-only table

Your intended protection is probably:

```text
entries is append-only

INSERT     ✓
UPDATE     ✗
DELETE     ✗
```

You may have triggers such as:

```sql
BEFORE UPDATE → RAISE error
BEFORE DELETE → RAISE error
```

That sounds safe.

But then your code uses:

```sql
INSERT OR REPLACE
```

and that's the loophole:

```text
                entries

Normal UPDATE ─────────────→ UPDATE trigger → BLOCKED ✓

Normal DELETE ─────────────→ DELETE trigger → BLOCKED ✓

INSERT OR REPLACE
      │
      └─ hidden DELETE
             │
             ├─ recursive_triggers ON  → trigger → BLOCKED ✓
             │
             └─ recursive_triggers OFF → skipped → overwrite ✗
```

That's what your screenshot is demonstrating.

---

# What I would change in the project

For an **append-only/immutable table**, I would avoid `INSERT OR REPLACE` entirely.

Instead use ordinary:

```sql
INSERT INTO entries(key, value)
VALUES ('a', 'three');
```

If `a` already exists, let the `PRIMARY KEY`/`UNIQUE` constraint reject it.

That matches the semantics you actually want:

```text
New key?
    ↓ yes
INSERT it

Existing key?
    ↓ yes
ERROR — do not touch old record
```

SQLite's default conflict behavior is `ABORT`, meaning the statement fails when the uniqueness constraint is violated rather than replacing the existing row. ([SQLite][2])

If duplicate inserts are expected and you simply want to ignore them, another possibility is:

```sql
INSERT OR IGNORE INTO entries(key, value)
VALUES ('a', 'three');
```

That means:

```text
key doesn't exist → insert

key already exists → leave existing row alone
```

rather than deleting it. SQLite documents `IGNORE` as skipping the conflicting row without changing it. ([SQLite][2])

---

## The key idea to remember

Your bug isn't really:

```text
"PRAGMA changed INSERT behavior"
```

It's:

```text
INSERT OR REPLACE
        ↓
is secretly
        ↓
DELETE conflicting row + INSERT new row
        ↓
and SQLite has a special rule:
REPLACE's DELETE trigger only fires when
recursive_triggers is enabled
```

So **`INSERT OR REPLACE` is a poor match for an immutable/append-only table**. Even though its name sounds like "insert, otherwise update," SQLite's actual conflict behavior involves deleting the existing row first. ([SQLite][2])

If the business rule is truly **"once written, this row can never change,"** ordinary `INSERT` plus uniqueness constraints is much easier to reason about than relying on `REPLACE` plus trigger settings.

[1]: https://sqlite.org/pragma.html "Pragma statements supported by SQLite"
[2]: https://sqlite.org/lang_conflict.html "The ON CONFLICT Clause"