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:

SELECT ...
INSERT ...
UPDATE ...
DELETE ...

PRAGMA changes or inspects how SQLite itself behaves.

For example:

PRAGMA recursive_triggers;

asks SQLite:

"Are recursive triggers enabled on this connection?"

It returns:

0   -- OFF

or:

1   -- ON

And:

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)

So mentally:

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

What's happening in your example

Imagine your table contains:

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:

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:

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:

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

SQLite's documentation explicitly describes REPLACE this way. (SQLite)

So:

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

is conceptually closer to:

DELETE FROM entries WHERE key = 'a';

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

than to:

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:

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)

This is the unintuitive bit.

So your two cases behave differently.

recursive_triggers = ON

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:

a → one

recursive_triggers = OFF

Now:

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:

a → OVERWRITTEN

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

Why is it called recursive_triggers?

This name makes the situation harder to understand.

Normally, "recursive triggers" means something like:

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)

So in this particular case, don't read:

recursive_triggers

as simply:

"Can my trigger call itself?"

Think:

"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:

entries is append-only

INSERT     ✓
UPDATE     ✗
DELETE     ✗

You may have triggers such as:

BEFORE UPDATE → RAISE error
BEFORE DELETE → RAISE error

That sounds safe.

But then your code uses:

INSERT OR REPLACE

and that's the loophole:

                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:

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:

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)

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

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

That means:

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)

The key idea to remember

Your bug isn't really:

"PRAGMA changed INSERT behavior"

It's:

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)

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.